按层级规则选取Order+Key组合记录的SQL查询实现方法

核心实现思路 #

利用窗口函数给每个Order+Key分组内的记录按规则权重排序，每个分组取排序首位的记录即可，单条记录的分组天然会返回自身，无需额外单独判断。3条规则的优先级权重从高到低设置为：

• 第一权重：code = '30'的记录权重最高，只要分组内存在该记录就排在最前
• 第二权重：number字段值倒序排列，无code='30'的分组内number值越大排序越靠前

通用SQL写法（支持MySQL8.0+、PostgreSQL、SQL Server等所有支持窗口函数的数据库） #

WITH ranked_result AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY `Order`, `Key`
            ORDER BY
                -- code='30'的记录标记为0，排在最前
                CASE WHEN code = '30' THEN 0 ELSE 1 END ASC,
                -- 无code='30'时按number倒序取最大值
                number DESC
        ) AS row_rank
    FROM your_actual_table -- 替换为实际业务表名
)
SELECT * FROM ranked_result WHERE row_rank = 1;

规则匹配验证 #

针对给出的样例场景，上述逻辑可完全匹配要求：

• 13+1、14+1分组：分组内无code='30'记录，排序后number值最大的记录排在首位，被选中
• 15+1分组：分组内存在code='30'的记录，该记录排序权重最高，无论number值为多少都会排在首位被选中，不会触发number取最大值规则
• 16+1分组：分组内仅1条记录，排序后row_rank固定为1，直接返回该记录

低版本数据库兼容写法（不支持窗口函数的场景，如MySQL5.x） #

如果使用的数据库版本不支持窗口函数，可以通过分组聚合后关联原表的方式实现，性能略低于窗口函数方案：

SELECT t.*
FROM your_actual_table t
INNER JOIN (
    SELECT
        `Order`,
        `Key`,
        -- 标记分组内是否存在code='30'的记录
        MAX(CASE WHEN code = '30' THEN 1 ELSE 0 END) AS exist_code30,
        -- 取分组内最大number值
        MAX(number) AS max_num
    FROM your_actual_table
    GROUP BY `Order`, `Key`
) group_temp
ON t.`Order` = group_temp.`Order` AND t.`Key` = group_temp.`Key`
WHERE
    (group_temp.exist_code30 = 1 AND t.code = '30')
    OR (group_temp.exist_code30 = 0 AND t.number = group_temp.max_num);

注意：如果业务场景中存在同一个Order+Key分组下有多条code='30'记录的异常情况，上述写法会默认取这类记录中number值最大的一条，可根据实际业务需求调整排序规则补充去重逻辑。

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