Oracle分组查询带过滤：实现rank数据行列转换的SQL语句求助

假设你的表名为dept_rank，可以使用以下查询语句得到你需要的结果：

WITH all_ranks AS (
    -- 生成1到8的rank序列，覆盖所有需要的列
    SELECT level AS rnk FROM dual CONNECT BY level <= 8
),
all_depts AS (
    -- 获取所有唯一部门
    SELECT DISTINCT dept FROM dept_rank
),
dept_rank_full AS (
    -- 补全每个部门的所有rank组合，判断是否存在对应记录
    SELECT 
        ad.dept,
        ar.rnk,
        CASE WHEN dr.id IS NOT NULL THEN 1 ELSE 0 END AS has_rank
    FROM all_depts ad
    CROSS JOIN all_ranks ar
    LEFT JOIN dept_rank dr ON ad.dept = dr.dept AND ar.rnk = dr."rank" -- 注意rank是关键字，需用双引号
),
pivoted_data AS (
    -- 转置rank行为列，拼接成指定格式
    SELECT 
        dept,
        "1" AS r1,
        "2" AS r2,
        "3" AS r3,
        "4" AS r4,
        "5" AS r5,
        "6" AS r6,
        "7" AS r7,
        "8" AS r8
    FROM dept_rank_full
    PIVOT (
        MAX(has_rank || '/' || rnk)
        FOR rnk IN (1,2,3,4,5,6,7,8)
    )
),
summary_row AS (
    -- 构造输出中的"dept"汇总行
    SELECT 
        'dept' AS dept,
        '1/1' AS r1,
        '1/2' AS r2,
        '1/3' AS r3,
        '1/4' AS r4,
        '1/5' AS r5,
        '1/6' AS r6,
        '1/7' AS r7,
        '1/8' AS r8
    FROM dual
)
-- 合并汇总行和部门数据行，按顺序输出
SELECT * FROM summary_row
UNION ALL
SELECT * FROM pivoted_data
ORDER BY 
    CASE dept WHEN 'dept' THEN 0 ELSE 1 END,
    dept;

关键步骤说明： #

• 生成全量rank序列：用CONNECT BY生成1到8的rank值，确保每个部门都能匹配所有需要的rank列。
• 补全部门-rank组合：通过交叉连接all_depts和all_ranks，得到每个部门对应所有rank的组合，再左连接原表判断是否存在该rank的记录。
• 行列转置：使用PIVOT将rank的行数据转成列，并拼接成存在标记/rank值的格式。
• 构造汇总行：单独生成输出中要求的dept行，再通过UNION ALL合并到结果集中，最后排序保证汇总行在最上方。

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