代码之家 › 专栏 › 技术社区 › Java

使用交叉应用同时显示两列

cross-apply tsql

0

Java · 技术社区 · 4 年前

我在底部有几乎相同的逻辑,但我无法组合起来显示两列。

我不知道如何将两个交叉应用程序查询合并为一个。

select


DateAdd(hour,hour_diff, ps)   punch_start
from [dbo].[Stage]
cross apply
(
values
( Try_Convert(datetime, punch_start) )
) x
(ps)
left join 
[dbo].[DIM] d on ps between d.start_dt and  d.end_dt

,
DateAdd(hour,hour_diff, ps1)  punch_end
from [dbo].[Stage]
cross apply
(
values
( Try_Convert(datetime, punch_end) )
) y
(ps1)
left join 
[dbo].[DIM] d on ps1 between d.start_dt and  d.end_dt


punch_start
Mar 29 2022  3:00AM
Mar 23 2022  6:28PM
Apr 11 2022  3:12AM
Apr  5 2022 10:18AM
Mar 30 2022  7:00AM
Apr  7 2022  2:57AM
Apr  6 2022  8:00PM
Mar 23 2022  2:44AM
Mar 24 2022 12:00PM
Apr 14 2022  7:18AM


punch_end
Mar 29 2022  7:50AM
Mar 23 2022  7:59PM
Apr 11 2022  9:33AM
Apr  5 2022  2:08PM
Mar 30 2022 10:39AM
Apr  7 2022  7:35AM
Apr  6 2022  9:32PM
Mar 23 2022  7:03AM
Mar 24 2022  7:01PM
Apr 14 2022  7:48AM

1 回复 | 直到 4 年前

1

D-Shih 4 年前

如果我理解正确的话,你可以试着把两个列合并在一起 VALUES

SELECT
    DateAdd(hour,hour_diff, ps)  punch_start,
    DateAdd(hour,hour_diff, ps1)  punch_end
FROM [dbo].[Stage]
CROSS APPLY
(
    values (Try_Convert(datetime, punch_start),Try_Convert(datetime, punch_end))
) x (ps,ps1)
left join 
[dbo].[DIM] d on 
    ps between d.start_dt and  d.end_dt
OR 
    ps1 between d.start_dt and  d.end_dt