如何在GROUP BY结果中查找组内日期未随行号排序的分组ID

核心判断规则：对每个id分组内的所有行，按line_no升序排序后，只要存在任意相邻行不满足"line_no更大的行，其date值不小于前一行的date值"，即判定该分组为date相对line_no未排序，提取这类分组的唯一id即可。

主流数据库通用实现（兼容MySQL 8.0+/PostgreSQL/SQL Server/Oracle等） #

利用窗口函数完成相邻行对比，代码可读性高、执行效率优：

SELECT DISTINCT id
FROM (
    SELECT 
        id,
        line_no,
        date,
        LAG(date) OVER (PARTITION BY id ORDER BY line_no) AS prev_row_date
    FROM AMAN
) sorted_rows
-- 存在逆序行即判定为未排序分组
WHERE date < prev_row_date;

实现逻辑拆解：

• LAG(date) OVER (PARTITION BY id ORDER BY line_no)：按id拆分独立分组，每个分组内按line_no从小到大排序，为每一行匹配取同分组内上一行的date值
• 只要当前行的date小于同分组上一行的date，就说明该分组的date序列没有按line_no顺序排列
• 外层DISTINCT id保证每个符合条件的分组仅返回一次对应id，和示例中仅输出30000的预期完全匹配

低版本数据库兼容实现（不支持窗口函数的场景，如MySQL 5.x） #

通过自关联匹配相邻行完成对比：

SELECT DISTINCT curr_row.id
FROM AMAN curr_row
INNER JOIN AMAN prev_row
    ON curr_row.id = prev_row.id 
    -- 匹配同id下，比当前行line_no小的最大line_no行，即相邻前一行
    AND prev_row.line_no = (
        SELECT MAX(line_no) 
        FROM AMAN 
        WHERE id = curr_row.id AND line_no < curr_row.line_no
    )
-- 存在逆序即判定为未排序
WHERE curr_row.date < prev_row.date;

性能优化提示 #

针对2万余行的AMAN表，可提前建立(id, line_no, date)联合索引，两种写法的查询响应时间均可控制在百毫秒级，不会出现慢查询。

注意：如果业务场景中允许同id下date值相等，当前判断逻辑可准确识别逆序；如果需要严格按升序要求（即哪怕相邻相等就算未排序），可将判断条件调整为date != prev_row_date即可。

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