时间戳

首页 > 解决方案 > 联合结果集

sql - 联合结果集

问题描述

我有一个返回结果集的查询现在我需要修改查询以返回另一个结果集

我已经运行了下面的代码，它会返回标签 ID 在两个 CTE 中的结果，并为您提供时间和超时。我现在需要修改查询，以便它会显示所有已扫描的标签，其中包含 Time in 和 no out time，反之亦然，而不会重复。

With CTE AS
( 
select Tag as 'Tag ID', UID_KEG, CONVERT(VARCHAR(10), MOVEMENT_DATE, 105) 
as [DATE],CONVERT(VARCHAR(10), MOVEMENT_DATE, 108) as [Time Out]
from MOVEMENT M
Inner join KEG K on K.UNIQUE_ID = M.UID_KEG
where Convert(varchar(10),MOVEMENT_DATE,120) = '2019-06-13' 
and UID_STATION = 4
and TAG <> 'NO TAG'
) ,
CTE2 AS
(select Tag as 'Tag ID', UID_KEG, CONVERT(VARCHAR(10), MOVEMENT_DATE, 105) 
as [DATE],CONVERT(VARCHAR(10), MOVEMENT_DATE, 108) as [Time IN]
from MOVEMENT M
Inner join KEG K on K.UNIQUE_ID = M.UID_KEG
where Convert(varchar(10),MOVEMENT_DATE,120) = '2019-06-13'
and UID_STATION = 5
and TAG <> 'NO TAG'
)
Select CTE.[Tag ID], CTE.[DATE], [Time IN], [Time Out],DATEDIFF(MINUTE, 
[Time IN], [Time Out]) as [Time in Process]
from CTE
Inner Join CTE2 on CTE2.[Tag ID] = CTE.[Tag ID]
where Exists (Select CTE2.[Tag ID] 
       from CTE2
       where CTE2.[Tag ID] = CTE.[Tag ID] )

此刻的查询给了我以下结果：

TAG ID                      DATE        Time_In     Time_Out    DIF
33154A36D00F46C000007144    6/13/2019   4:43:05 AM  6:25:27 AM  102
33154A36D00F46C00000464A    6/13/2019   4:43:47 AM  6:06:45 AM  83
33154A36D00F46C000006DFF    6/13/2019   4:46:22 AM  6:25:27 AM  99
33154A36D00F46C0000040A8    6/13/2019   4:54:23 AM  6:10:55 AM  76
33154A36D00F46C000002ECB    6/13/2019   4:55:59 AM  6:10:55 AM  75
33154A36D00F46C000002A2F    6/13/2019   5:03:18 AM  6:20:40 AM  77
33154A36D00F46C000000499    6/13/2019   5:34:35 AM  6:25:27 AM  51
33154A36D00F46C00000627C    6/13/2019   5:38:04 AM  6:25:27 AM  47
33154A36D00F46C000006F74    6/13/2019   5:38:06 AM  6:28:42 AM  50

我现在正在寻找以下要返回的内容：

33154A36D00F46C000006F38    6/13/2019              6:28:42 AM   
33154A36D00F46C000006F62    6/13/2019              6:47:42 AM   
33154A36D00F46C000006F90    6/13/2019              7:47:12 AM

标签： sqlsql-servertsql

解决方案

为什么不简化查询，只做一次？

select 
    Tag as 'Tag ID'
    , UID_KEG
    , max(iif(UID_STATION=4,CONVERT(VARCHAR(10), MOVEMENT_DATE, 105),null)) as [DATE]
    , max(iif(UID_STATION=4,CONVERT(VARCHAR(10), MOVEMENT_DATE, 108),null)) as [Time Out]
    , max(iif(UID_STATION=5,CONVERT(VARCHAR(10), MOVEMENT_DATE, 108),null)) as [Time IN]
from MOVEMENT M
Inner join KEG K on 
    K.UNIQUE_ID = M.UID_KEG
    AND Convert(varchar(10),MOVEMENT_DATE,120) = '2019-06-13' 
    and UID_STATION in (4,5)
    and TAG <> 'NO TAG'
group by 
    Tag 
    , UID_KEG

如果你想保留 cte，那么首先，因为你正在做一个内部连接，你可能会失去这个：

where Exists (Select CTE2.[Tag ID] 
       from CTE2
       where CTE2.[Tag ID] = CTE.[Tag ID] )

如果您在 [Tag ID] 上进行内部联接，则只能在两个表中获得值

我会丢失 where 子句，并将其更改为外连接，在这种情况下，您将获得所有标签 ID，无论它们是在还是在外，或者可能是左外连接，如果您希望所有标签都带有 in 日期，例如这个：

With CTE AS
( 
select Tag as 'Tag ID', UID_KEG, CONVERT(VARCHAR(10), MOVEMENT_DATE, 105) 
as [DATE],CONVERT(VARCHAR(10), MOVEMENT_DATE, 108) as [Time Out]
from MOVEMENT M
Inner join KEG K on K.UNIQUE_ID = M.UID_KEG
where Convert(varchar(10),MOVEMENT_DATE,120) = '2019-06-13' 
and UID_STATION = 4
and TAG <> 'NO TAG'
) ,
CTE2 AS
(select Tag as 'Tag ID', UID_KEG, CONVERT(VARCHAR(10), MOVEMENT_DATE, 105) 
as [DATE],CONVERT(VARCHAR(10), MOVEMENT_DATE, 108) as [Time IN]
from MOVEMENT M
Inner join KEG K on K.UNIQUE_ID = M.UID_KEG
where Convert(varchar(10),MOVEMENT_DATE,120) = '2019-06-13'
and UID_STATION = 5
and TAG <> 'NO TAG'
)
Select CTE.[Tag ID], CTE.[DATE], [Time IN], [Time Out],DATEDIFF(MINUTE, 
[Time IN], [Time Out]) as [Time in Process]
from CTE
LEFT Join CTE2 on CTE2.[Tag ID] = CTE.[Tag ID]

--- 在编辑中添加

如果您想为您的进出时间设置一个 dedediff，如何：

SELECT
  Tag AS 'Tag ID',
  UID_KEG,
  MAX(iif(UID_STATION = 4, CONVERT(varchar(10), MOVEMENT_DATE, 105), NULL)) AS [DATE],
  MAX(iif(UID_STATION = 4, CONVERT(varchar(10), MOVEMENT_DATE, 108), NULL)) AS [Time Out],
  MAX(iif(UID_STATION = 5, CONVERT(varchar(10), MOVEMENT_DATE, 108), NULL)) AS [Time IN],
  DATEDIFF(MINUTE,
    MAX(iif(UID_STATION = 4, MOVEMENT_DATE, NULL)),
    MAX(iif(UID_STATION = 5, MOVEMENT_DATE, NULL))) AS [Time Elapsed]
FROM MOVEMENT M
INNER JOIN KEG K
  ON K.UNIQUE_ID = M.UID_KEG
  AND CONVERT(varchar(10), MOVEMENT_DATE, 120) = '2019-06-13'
  AND UID_STATION IN (4, 5)
  AND TAG <> 'NO TAG'
GROUP BY Tag,
         UID_KEG

推荐阅读