SQL查询修改需求：补充显示仅入/出时间的Tag记录（无重复）

没问题，我帮你调整这个SQL查询，让它既能返回2019-06-13当天同时有入站（UID_STATION=5）和出站（UID_STATION=4）的Tag记录，也能补充显示当天只有入站或只有出站的Tag条目，并且保证结果没有重复。

原查询代码 #

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] )

修改后的查询代码 #

WITH CTE_Out 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'
),
CTE_In 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 DISTINCT
       COALESCE(CTE_Out.[Tag ID], CTE_In.[Tag ID]) AS [Tag ID],
       COALESCE(CTE_Out.[DATE], CTE_In.[DATE]) AS [DATE],
       CTE_In.[Time IN],
       CTE_Out.[Time Out],
       CASE 
           WHEN CTE_In.[Time IN] IS NOT NULL AND CTE_Out.[Time Out] IS NOT NULL 
               THEN DATEDIFF(MINUTE, CTE_In.[Time IN], CTE_Out.[Time Out])
           ELSE NULL 
       END AS [Time in Process]
FROM CTE_Out
FULL OUTER JOIN CTE_In ON CTE_Out.[Tag ID] = CTE_In.[Tag ID]

关键修改说明 #

• 将原有的INNER JOIN替换为FULL OUTER JOIN：这样就能同时保留只在出站表（CTE_Out）、只在入站表（CTE_In）以及两边都存在的Tag记录。
• 使用COALESCE函数统一日期和Tag ID字段：确保当某一边的记录为空时，能取到另一边的有效值，避免显示NULL。
• 新增CASE语句处理处理时长：只有当入站和出站时间都存在时才计算时长，否则返回NULL，符合实际业务逻辑。
• 添加DISTINCT关键字：防止因为数据重复导致的结果重复（如果你的数据里同一个Tag当天有多条同方向记录，可能需要进一步调整，但这个关键字能先保证结果无重复）。
• 移除多余的EXISTS子句：全外连接已经自然包含了所有需要的记录，不需要额外过滤。

修改后预期结果 #

除了原查询返回的同时有出入站的记录外，还会补充以下仅单方向的记录：

（注：仅入站的记录，Time Out和Time in Process会显示NULL；仅出站的记录则Time In和Time in Process显示NULL）

内容的提问来源于stack exchange，提问作者Censor1983