SQL Server中如何保留同一ID下首条Type=0记录并排除其余同类型记录

需求说明 #

编写SELECT查询，显示所有记录，但需排除同一ID下Type列值为0的第一条记录之外的所有Type=0记录。

当前数据 #

ID  Type    Name
123 0   Sathiya
123 1   Kumar
123 NULL    Mohan
123 6   Ranjith
124 0   Sathiya
123 0   John
125 6   Albert
123 0   Sathiya
124 0   Kumar
124 0   Joseph

预期结果 #

ID  Type    Name
123 0   Sathiya
123 1   Kumar
123 NULL    Mohan
123 6   Ranjith
124 0   Sathiya
125 6   Albert

现有查询问题 #

现有查询会对所有Type进行分区，无法仅针对Type=0的记录处理，因此无法得到预期结果：

SELECT
ROW_NUMBER() PARTITION BY ID,Type ORDER BY StartTime ASC AS RN,
*
INTO #temp1
FROM TABLE

SELECT 
*
FROM #temp1
WHERE RN>1

可行解决方案 #

方案一：CTE+CASE标记行号 #

通过CASE语句仅为Type=0的记录生成按ID分区的行号，其他记录统一标记为行号1，最后筛选行号为1的记录即可：

WITH RankedRecords AS (
    SELECT 
        ID,
        Type,
        Name,
        CASE 
            WHEN Type = 0 THEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY StartTime ASC)
            ELSE 1
        END AS RN
    FROM YourTable
)
SELECT ID, Type, Name
FROM RankedRecords
WHERE RN = 1;

方案二：UNION ALL拼接结果集 #

分两部分查询后拼接：第一部分取每个ID下第一条Type=0的记录，第二部分取所有非Type=0的记录：

-- 取每个ID下第一条Type=0的记录
SELECT ID, Type, Name
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY StartTime ASC) AS RN
    FROM YourTable
    WHERE Type = 0
) t
WHERE RN = 1

UNION ALL

-- 取所有非Type=0的记录（包括Type为NULL的情况）
SELECT ID, Type, Name
FROM YourTable
WHERE Type != 0 OR Type IS NULL;

两种方案都能实现需求，方案一代码更简洁，方案二逻辑更直观，可根据实际场景选择。

内容的提问来源于stack exchange，提问作者Sathiya Kumar V M