PostgreSQL 9.3实现分区查询的语言统计求和（无需Pivot）

你的现有查询已经用dense_rank()和条件聚合完美实现了行转列，把每个学生的多语言数据转换成了固定的3列格式。要添加你需要的总计行，我们有两种贴合PostgreSQL 9.3特性的可行方法，下面详细说明：

方法1：用GROUP BY ROLLUP直接生成总计行 #

这种方法能在同一个查询里同时输出学生明细和总计，不需要额外拼接子查询，效率更高：

WITH t AS (
    SELECT 
        s.studentnumber AS studentnr, 
        p.firstname AS name, 
        sl.gradenumber AS gradenumber, 
        l.text AS language,
        dense_rank() OVER (
            PARTITION BY s.studentnumber, p.firstname, sl.gradenumber 
            ORDER BY l.text
        ) AS seqnum
    FROM student s
    JOIN pupil p ON p.id = s.pupilid
    JOIN pupillanguage pl ON pl.pupilid = p.id
    JOIN language l ON l.id = pl.languageid
    JOIN schoollevel sl ON sl.id = p.schoollevelid
)
SELECT
    -- 总计行显示'Total'，明细行显示学生编号
    CASE WHEN GROUPING(studentnr) = 1 THEN 'Total' ELSE studentnr END AS studentnr,
    CASE WHEN GROUPING(name) = 1 THEN '' ELSE name END AS name,
    CASE WHEN GROUPING(gradenumber) = 1 THEN '' ELSE gradenumber END AS gradenumber,
    -- 明细行保留语言值，总计行统计该列非空学生数
    CASE 
        WHEN GROUPING(studentnr) = 1 THEN COUNT(CASE WHEN seqnum = 1 THEN language END)
        ELSE MAX(CASE WHEN seqnum = 1 THEN language END)
    END AS language_1,
    CASE 
        WHEN GROUPING(studentnr) = 1 THEN COUNT(CASE WHEN seqnum = 2 THEN language END)
        ELSE MAX(CASE WHEN seqnum = 2 THEN language END)
    END AS language_2,
    CASE 
        WHEN GROUPING(studentnr) = 1 THEN COUNT(CASE WHEN seqnum = 3 THEN language END)
        ELSE MAX(CASE WHEN seqnum = 3 THEN language END)
    END AS language_3
FROM t
GROUP BY ROLLUP(studentnr, name, gradenumber)
ORDER BY 
    -- 让总计行排在最后
    CASE WHEN GROUPING(studentnr) = 1 THEN 1 ELSE 0 END,
    studentnr;

核心细节： #

• GROUPING(studentnr)：当该行是ROLLUP生成的汇总行时返回1，明细行返回0，用来区分两种行的逻辑。
• COUNT(CASE ...)：自动忽略NULL值，正好统计对应语言列有多少学生填写了语言。

方法2：用UNION ALL拼接明细和汇总结果 #

如果你对ROLLUP不太熟悉，这种更直观的写法也能达到目的：

WITH t AS (
    SELECT 
        s.studentnumber AS studentnr, 
        p.firstname AS name, 
        sl.gradenumber AS gradenumber, 
        l.text AS language,
        dense_rank() OVER (
            PARTITION BY s.studentnumber, p.firstname, sl.gradenumber 
            ORDER BY l.text
        ) AS seqnum
    FROM student s
    JOIN pupil p ON p.id = s.pupilid
    JOIN pupillanguage pl ON pl.pupilid = p.id
    JOIN language l ON l.id = pl.languageid
    JOIN schoollevel sl ON sl.id = p.schoollevelid
),
student_languages AS (
    -- 复用你原有的行转列查询
    SELECT
        studentnr,
        name,
        gradenumber,
        MAX(CASE WHEN seqnum = 1 THEN language END) AS language_1,
        MAX(CASE WHEN seqnum = 2 THEN language END) AS language_2,
        MAX(CASE WHEN seqnum = 3 THEN language END) AS language_3
    FROM t
    GROUP BY studentnr, name, gradenumber
)
-- 先输出学生明细行
SELECT studentnr, name, gradenumber, language_1, language_2, language_3
FROM student_languages
UNION ALL
-- 再追加总计行
SELECT
    'Total' AS studentnr,
    '' AS name,
    '' AS gradenumber,
    COUNT(language_1) AS language_1,
    COUNT(language_2) AS language_2,
    COUNT(language_3) AS language_3
FROM student_languages
ORDER BY 
    CASE WHEN studentnr = 'Total' THEN 1 ELSE 0 END,
    studentnr;

核心细节： #

• 先把你的原查询结果存入student_languages临时表，再用COUNT()统计各列非空值数量生成总计行。
• UNION ALL保证明细和总计行完整保留，不会去重。

快速理解partition和dense_rank #

你提到对这两个概念陌生，这里用大白话解释：

• PARTITION BY：把整个数据集按指定字段拆分成小分组（这里是每个学生单独一组），窗口函数只会在组内计算。
• dense_rank()：在每个分组内给行排名，相同内容会得到相同排名，且排名是连续的（比如两个第一，下一个直接是第二，不会跳过数字）。这里用它给每个学生的语言排序，才能把第N个语言放到对应的language_N列里。

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