如何从SQL分组数据中筛选含NULL且非最高Id的记录？

先结合你的需求和示例结果，我们要找出满足以下规则的Id：

• 该Id不是对应BodyNumber分组中的最高Id；
• 该Id对应的DeletedRegNumber为NULL；
• 所在BodyNumber分组的最高Id对应的DeletedRegNumber不为NULL（即分组里的NULL值出现在非最高Id行）

示例测试数据 #

先把你的测试表和数据整理成可直接运行的代码块：

declare @datas table(BodyNumber int, Id int, RegNumber bigint, DeletedRegNumber bigint null) 
insert into @datas values(70, 355, 559, 559) 
insert into @datas values(70, 581, 660, NULL) 
insert into @datas values(53, 424, 354, 354) 
insert into @datas values(53, 429, 364, 364) 
insert into @datas values(60, 395, 519, NULL) 
insert into @datas values(60, 428, 296, 296) 
insert into @datas values(65, 398, 518, NULL) 
insert into @datas values(65, 430, 256, 256)

解决方案SQL #

这里用CTE先计算每个分组的核心信息，再关联原表筛选目标记录：

WITH GroupMaxInfo AS (
    SELECT 
        BodyNumber,
        MAX(Id) AS MaxGroupId,
        -- 获取分组最高Id对应的DeletedRegNumber
        MAX(CASE WHEN Id = MAX(Id) OVER (PARTITION BY BodyNumber) THEN DeletedRegNumber END) AS MaxIdDeletedReg
    FROM @datas
    GROUP BY BodyNumber
)
SELECT d.Id
FROM @datas d
JOIN GroupMaxInfo gmi ON d.BodyNumber = gmi.BodyNumber
WHERE 
    -- 排除分组内的最高Id
    d.Id <> gmi.MaxGroupId
    -- 当前行的DeletedRegNumber为NULL
    AND d.DeletedRegNumber IS NULL
    -- 确保分组最高Id的DeletedRegNumber不为NULL
    AND gmi.MaxIdDeletedReg IS NOT NULL;

逻辑说明 #

1. CTE GroupMaxInfo：
   • 按BodyNumber分组，计算每个分组的最高Id（MaxGroupId）；
   • 通过窗口函数定位分组内的最高Id，取出其对应的DeletedRegNumber（MaxIdDeletedReg），用来判断分组的NULL值是否仅出现在最高Id行。
2. 主查询：
   • 关联原表和CTE，筛选出非最高Id的行；
   • 锁定DeletedRegNumber为NULL的行；
   • 排除那些NULL值仅出现在最高Id行的分组（比如示例中的分组70）。

执行该查询后，会返回你需要的结果：395和398。

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