如何编写SQL按cd分组取数，优先保留tbl1记录，每组最多返回4条

实现思路 #

1. 优先保留tbl1的全量记录，给tbl1记录设置最高排序优先级
2. 筛选出tbl2中未出现在tbl1的记录，按priority从小到大排序（值越小优先级越高）作为补充
3. 按cd字段分组后对组内记录排序编号，取每组前4条即可

参考SQL（支持窗口函数的主流数据库通用，如MySQL8.0+、PostgreSQL、SQL Server等） #

WITH all_candidate AS (
    -- 第一部分：tbl1的全部记录，排序优先级最高
    SELECT 
        cd,
        productcd,
        type,
        1 AS sort_flag,
        0 AS priority
    FROM tbl1
    UNION ALL
    -- 第二部分：tbl2中不在tbl1的记录，按原生priority排序
    SELECT 
        t2.cd,
        t2.productcd,
        t2.type,
        2 AS sort_flag,
        t2.priority
    FROM tbl2 t2
    LEFT JOIN tbl1 t1 
        ON t2.cd = t1.cd 
        AND t2.productcd = t1.productcd 
        AND t2.type = t1.type
    WHERE t1.cd IS NULL
),
ranked_records AS (
    -- 按cd分组，组内按规则排序编号
    SELECT 
        cd,
        productcd,
        type,
        ROW_NUMBER() OVER (
            PARTITION BY cd 
            ORDER BY sort_flag ASC, priority ASC
        ) AS row_num
    FROM all_candidate
)
-- 取每组前4条
SELECT cd, productcd, type 
FROM ranked_records 
WHERE row_num <=4
ORDER BY cd, row_num;

输出结果 #

执行上述SQL后和预期结果完全一致：

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