Oracle SQL：如何根据选课组匹配对应学分并按日期排序展示

核心思路是先把Credits表的列转行（逆透视），让每个课程组的学分变成单独的行记录，再和GrpChoice表关联，筛选出学生实际选择的课程组，最后按选组日期调整排序。

步骤1：逆透视Credits表 #

使用Oracle的UNPIVOT操作，将原本的多列学分（ArtCred、BizCred等）转换为每行对应一个课程组和学分的结构，同时统一课程组命名格式，和GrpChoice中的GroupChoice匹配：

SELECT 
    ID,
    UPPER(REPLACE(GroupCol, 'Cred', '')) AS CourseGroup,
    Credit
FROM Credits
UNPIVOT (
    Credit FOR GroupCol IN (
        ArtCred AS 'ArtCred',
        BizCred AS 'BizCred',
        HumCred AS 'HumCred',
        NatCred AS 'NatCred',
        TekCred AS 'TekCred',
        GenCred AS 'GenCred'
    )
) unpvt;

这段代码会把每个学生的多条学分列拆成单行记录，比如ID5001会生成(5001, 'ART', 12)、(5001, 'BIZ',7.5)等行。

步骤2：关联筛选并排序 #

将逆透视后的结果和GrpChoice表通过ID、课程组名称关联，只保留学生选择过的组，再按需求排序：

SELECT 
    gc.GroupChoice,
    uc.Credit
FROM (
    -- 逆透视Credits表的子查询
    SELECT 
        ID,
        UPPER(REPLACE(GroupCol, 'Cred', '')) AS CourseGroup,
        Credit
    FROM Credits
    UNPIVOT (
        Credit FOR GroupCol IN (
            ArtCred AS 'ArtCred',
            BizCred AS 'BizCred',
            HumCred AS 'HumCred',
            NatCred AS 'NatCred',
            TekCred AS 'TekCred',
            GenCred AS 'GenCred'
        )
    ) unpvt
) uc
JOIN GrpChoice gc 
    ON uc.ID = gc.ID 
    AND uc.CourseGroup = gc.GroupChoice
ORDER BY gc.ID, gc.DateChoice ASC; -- 要倒序就改成DESC

结果验证 #

• ID5001按日期正序输出（和你的示例一致）：

ART 12
BIZ 7.5
NAT 14

• ID5002输出：

ART 10.5

补充说明 #

• 用UPPER()/LOWER()统一课程组名称的大小写，避免因大小写差异导致关联失败。
• 你之前尝试的PIVOT是行转列操作，这里需要的是相反的列转行，所以UNPIVOT才是正确方向。

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