如何修改SQL查询找出同一headId下日期不同的记录

原查询语句 #

select 
    item.HeadID as headId,
    item.STARTDate as itemStartDate,
    item.ENDDate as itemEndDate,
from 
    serv.HEAD head
inner join 
    serv.ITEM item on item.HeadID = head.HeadID

原查询结果 #

目标需求 #

找出同一headId对应的itemStartDate或itemEndDate存在差异的行，例如：

修改后的查询方案 #

方案一：先定位问题headId再取数据 #

这种方式先筛选出存在日期不一致的headId，再关联获取对应的所有行，适合数据量较大的场景，性能更优：

WITH problematic_heads AS (
    SELECT item.HeadID
    FROM serv.HEAD head
    INNER JOIN serv.ITEM item ON item.HeadID = head.HeadID
    GROUP BY item.HeadID
    HAVING COUNT(DISTINCT item.STARTDate) > 1 
        OR COUNT(DISTINCT item.ENDDate) > 1
)
SELECT 
    item.HeadID AS headId,
    item.STARTDate AS itemStartDate,
    item.ENDDate AS itemEndDate
FROM serv.HEAD head
INNER JOIN serv.ITEM item ON item.HeadID = head.HeadID
INNER JOIN problematic_heads ph ON ph.HeadID = item.HeadID
ORDER BY item.HeadID, item.STARTDate, item.ENDDate;

方案二：用窗口函数直接筛选 #

通过窗口函数统计每个headId下的日期变体数量，一步到位筛选出目标行，写法更简洁：

SELECT 
    headId,
    itemStartDate,
    itemEndDate
FROM (
    SELECT 
        item.HeadID AS headId,
        item.STARTDate AS itemStartDate,
        item.ENDDate AS itemEndDate,
        COUNT(DISTINCT item.STARTDate) OVER (PARTITION BY item.HeadID) AS start_date_variants,
        COUNT(DISTINCT item.ENDDate) OVER (PARTITION BY item.HeadID) AS end_date_variants
    FROM serv.HEAD head
    INNER JOIN serv.ITEM item ON item.HeadID = head.HeadID
) AS data
WHERE start_date_variants > 1 OR end_date_variants > 1
ORDER BY headId, itemStartDate, itemEndDate;

内容的提问来源于stack exchange，提问作者Fakhar Ahmad Rasul