SQL Server按ID计算分类对应日期月数差的实现问题

问题描述 #

我正在使用Microsoft SQL Server Management Studio，需新增一列完成以下计算：当category为'Exec'时取enddate，为'Scop'时取startdate，计算同一id下这两个日期的月数差。要求SQL按id分别计算，每个id得到对应结果。但当前SQL取全表的最小enddate和最小startdate，导致所有id的计算结果相同。

原SQL语句 #

SELECT
    id, category, startdate, enddate,
    CASE
        WHEN id = id 
            THEN DATEDIFF(month, 
                          (SELECT MIN(enddate) from [A].[PP] where category = 'Exec'),
                          (SELECT MIN(startdate) from [A].[PP] where category = 'Scop')) --AS datemodify
            ELSE NULL
    END
FROM
    [A].[PP]
WHERE
    startdate IS NOT NULL
    AND (category = 'Exec' OR category = 'Scop')
ORDER BY 
    id ASC

当前输出结果 #

期望输出结果 #

修正方案 #

方法1：关联子查询（匹配当前ID） #

给子查询添加id = t.id的关联条件，让每个子查询仅获取当前ID对应分类的日期：

SELECT
    t.id, t.category, t.startdate, t.enddate,
    DATEDIFF(month,
             (SELECT MIN(enddate) FROM [A].[PP] WHERE category = 'Exec' AND id = t.id),
             (SELECT MIN(startdate) FROM [A].[PP] WHERE category = 'Scop' AND id = t.id)) AS NewColumn
FROM
    [A].[PP] t
WHERE
    t.startdate IS NOT NULL
    AND t.category IN ('Exec', 'Scop')
ORDER BY 
    t.id ASC

方法2：先分组聚合再关联（性能更优） #

先按ID分组，计算每个ID对应的Exec的enddate和Scop的startdate，再关联回原表：

WITH IdDateAgg AS (
    SELECT
        id,
        MIN(CASE WHEN category = 'Exec' THEN enddate END) AS ExecEndDate,
        MIN(CASE WHEN category = 'Scop' THEN startdate END) AS ScopStartDate
    FROM [A].[PP]
    WHERE category IN ('Exec', 'Scop') AND startdate IS NOT NULL
    GROUP BY id
)
SELECT
    t.id, t.category, t.startdate, t.enddate,
    DATEDIFF(month, a.ExecEndDate, a.ScopStartDate) AS NewColumn
FROM [A].[PP] t
JOIN IdDateAgg a ON t.id = a.id
WHERE t.startdate IS NOT NULL AND t.category IN ('Exec', 'Scop')
ORDER BY t.id ASC

问题原因说明 #

原SQL中的子查询没有限定id条件，导致无论当前行属于哪个ID，都会取全表所有Exec分类的最小enddate和所有Scop分类的最小startdate，因此所有行的计算结果完全一致。修正后通过关联ID或分组聚合，确保每个ID仅使用自身对应的日期进行计算。

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