SQL自连接查询问题：求两类工单状态首次设置的时间差

问题描述 #

需要计算工单首次设置HD05/SD05/HD04状态的时间，与首次设置HD10/SD10状态的时间差。表中状态可重复设置，必须取首次出现的时间。当前使用自连接查询时：

• 不对HD10/SD10取最小时间会返回重复结果；
• 添加子查询限制HD10/SD10取最小时间时，无结果返回。

表结构示例 #

原有问题SQL #

当前使用的自连接查询：

SELECT DATEDIFF(MINUTE, FTa.Date, FTb.Date) AS Difference,FTa.Date,FTb.Date
FROM [dbo].[FT] FTa
INNER JOIN [dbo].[FT] FTb
ON FTa.Ticket_No = FTb.Ticket_No
WHERE FTa.Date IN (
    SELECT min(x.Date)
    FROM FT x
    WHERE x.Ticket_No = FTa.Ticket_No AND FTa.Status in ('HD04','HD05','SD05')
    ) 
AND FTb.Status IN ('HD10','SD10') AND DATEDIFF(MINUTE,FTa.Date, FTb.Date)>30

添加以下子查询限制FTb取最小时间时无结果：

SELECT min(x.Date)
FROM FT x
WHERE x.Ticket_No = FTb.Ticket_No AND FTb.Status IN ('HD10','SD10')

问题原因 #

添加的子查询逻辑错误：子查询中错误使用外部表FTb的Status作为过滤条件，而非子查询表x的Status。这导致子查询无法正确筛选出HD10/SD10的首次时间。

解决方案 #

方案1：使用CTE预聚合首次时间（推荐） #

先通过CTE分别计算每个工单的两类状态首次时间，再关联计算差值，避免自连接的重复问题：

WITH FirstStatusTimes AS (
    SELECT 
        Ticket_Nummer,
        MIN(CASE WHEN Status IN ('HD04','HD05','SD05') THEN Datum END) AS First_Early_Status,
        MIN(CASE WHEN Status IN ('HD10','SD10') THEN Datum END) AS First_Late_Status
    FROM [dbo].[FT]
    GROUP BY Ticket_Nummer
)
SELECT 
    Ticket_Nummer,
    DATEDIFF(MINUTE, First_Early_Status, First_Late_Status) AS Time_Difference_Minutes,
    First_Early_Status,
    First_Late_Status
FROM FirstStatusTimes
WHERE 
    First_Early_Status IS NOT NULL 
    AND First_Late_Status IS NOT NULL
    AND DATEDIFF(MINUTE, First_Early_Status, First_Late_Status) > 30;

方案2：子查询预聚合后自连接 #

分别通过子查询获取两类状态的首次时间，再进行连接：

SELECT 
    DATEDIFF(MINUTE, FTa.First_Early, FTb.First_Late) AS Difference,
    FTa.First_Early,
    FTb.First_Late
FROM (
    SELECT Ticket_Nummer, MIN(Datum) AS First_Early
    FROM [dbo].[FT]
    WHERE Status IN ('HD04','HD05','SD05')
    GROUP BY Ticket_Nummer
) FTa
INNER JOIN (
    SELECT Ticket_Nummer, MIN(Datum) AS First_Late
    FROM [dbo].[FT]
    WHERE Status IN ('HD10','SD10')
    GROUP BY Ticket_Nummer
) FTb ON FTa.Ticket_Nummer = FTb.Ticket_Nummer
WHERE DATEDIFF(MINUTE, FTa.First_Early, FTb.First_Late) > 30;

这两种方案都能准确获取每个工单的两类状态首次时间，避免重复结果或无结果的问题。

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