使用SQL按ID分组判断Sort字段连续性并选取对应记录

核心逻辑说明 #

判断同ID的Sort字段是否连续的核心依据：连续整数序列满足 最大值 - 最小值 + 1 = 序列元素总数，如果等式不成立则说明序列存在大于1的间隔。

• 序列连续：取Sort最小值对应的记录
• 序列存在间隔：取Sort最大值对应的记录

通用实现代码（兼容所有支持子查询/CTE的主流数据库） #

WITH id_stat AS (
    -- 先按ID分组计算统计值，确定每个ID需要取的目标Sort值
    SELECT
        id,
        CASE
            WHEN MAX(sort) - MIN(sort) + 1 = COUNT(*) THEN MIN(sort)
            ELSE MAX(sort)
        END AS target_sort
    FROM 你的表名
    GROUP BY id
)
-- 关联原表取目标Sort对应的完整记录
SELECT t.*
FROM 你的表名 t
INNER JOIN id_stat s
    ON t.id = s.id
    AND t.sort = s.target_sort;

低版本数据库兼容写法（不支持CTE的场景，如MySQL 5.x） #

直接把子查询写在关联逻辑中即可：

SELECT t.*
FROM 你的表名 t
INNER JOIN (
    SELECT
        id,
        CASE
            WHEN MAX(sort) - MIN(sort) + 1 = COUNT(*) THEN MIN(sort)
            ELSE MAX(sort)
        END AS target_sort
    FROM 你的表名
    GROUP BY id
) s ON t.id = s.id AND t.sort = s.target_sort;

注意事项 #

• 如果同ID同Sort存在重复记录，上述语句会返回所有匹配的重复记录，可根据业务需求增加DISTINCT或额外筛选条件去重
• 若Sort字段存在NULL值，会导致COUNT统计不准，建议先增加过滤条件排除Sort为NULL的记录

内容的提问来源于stack exchange，提问作者SQL-Developer