Oracle SQL中如何按类别分组合并科目与类别透视查询？

我可以在Excel中实现按类别分组的透视查询，但希望在Oracle SQL中完成该操作。现有学生表table_student，已能分别写出以下两个透视查询：

1. 按科目统计成绩总和：

select * from (select studentID, subject, grade from table_student)
PIVOT 
(
sum (grade) FOR (subject) IN ('A','B','C')
)
order by studentID;

2. 按类别统计成绩总和：

select * from (select studentID, category, grade from table_student)
PIVOT 
(
sum (grade) FOR (category) IN ('Common','Science')
)
order by studentID;

现在想合并这两个查询，得到包含类别分组下科目统计的目标结果，请问能否通过Oracle SQL实现？

当然可以实现，以下是两种实用方案：

方案一：多维度联合透视（推荐） #

Oracle支持在PIVOT子句中同时指定多个维度列，直接将category和subject组合作为透视维度，一次性生成类别分组下的科目成绩汇总：

select * 
from (
    select studentID, category, subject, grade 
    from table_student
)
PIVOT (
    sum(grade) 
    FOR (category, subject) IN (
        ('Common', 'A') AS Common_A,
        ('Common', 'B') AS Common_B,
        ('Common', 'C') AS Common_C,
        ('Science', 'A') AS Science_A,
        ('Science', 'B') AS Science_B,
        ('Science', 'C') AS Science_C
    )
)
order by studentID;

该查询会生成Common_A、Science_B这类列，每个列对应某类别下某科目的成绩总和，完全匹配你需要的"类别分组下科目统计"需求。

方案二：关联两个透视结果 #

如果需要同时保留原有两种透视的列结构，可通过studentID将两个查询结果关联：

select 
    p1.studentID,
    p1."A", p1."B", p1."C",
    p2."Common", p2."Science"
from (
    select * from (select studentID, subject, grade from table_student)
    PIVOT (sum(grade) FOR (subject) IN ('A','B','C'))
) p1
join (
    select * from (select studentID, category, grade from table_student)
    PIVOT (sum(grade) FOR (category) IN ('Common','Science'))
) p2
on p1.studentID = p2.studentID
order by p1.studentID;

这种方式会同时展示每个学生的各科成绩总和、各分类成绩总和，适合需要保留双维度汇总的场景。若存在学生在某类别/科目无成绩的情况，建议改用FULL OUTER JOIN避免数据丢失。

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