SQL查询结果行转列

练习脚本

    -- 学生表

    CREATE TABLE student (

      stuid VARCHAR(16) NOT NULL,

      stunm VARCHAR(20) NOT NULL,

      PRIMARY KEY (stuid)

    );

    -- 课程表

    CREATE TABLE courses (

      courseno VARCHAR(20) NOT NULL,

      coursenm VARCHAR(100) NOT NULL,

      PRIMARY KEY (courseno)

    );

    -- 成绩表

    CREATE TABLE score (

      stuid VARCHAR(16) NOT NULL,

      courseno VARCHAR(20) NOT NULL,

      scores FLOAT NULL DEFAULT NULL,

      PRIMARY KEY (stuid, courseno)

    );

    -- 插入数据

    -- 学生表数据

    Insert Into student (stuid, stunm) Values('1001', '张三');

    Insert Into student (stuid, stunm) Values('1002', '李四');

    Insert Into student (stuid, stunm) Values('1003', '赵二');

    Insert Into student (stuid, stunm) Values('1004', '王五');

    Insert Into student (stuid, stunm) Values('1005', '刘青');

    Insert Into student (stuid, stunm) Values('1006', '周明');

    -- 课程表数据

    Insert Into courses (courseno, coursenm) Values('C001', '大学语文');

    Insert Into courses (courseno, coursenm) Values('C002', '新视野英语');

    Insert Into courses (courseno, coursenm) Values('C003', '离散数学');

    Insert Into courses (courseno, coursenm) Values('C004', '概率论与数理统计');

    Insert Into courses (courseno, coursenm) Values('C005', '线性代数');

    Insert Into courses (courseno, coursenm) Values('C006', '高等数学(一)');

    Insert Into courses (courseno, coursenm) Values('C007', '高等数学(二)');

    -- 成绩表数据

    Insert Into score(stuid, courseno, scores) Values('1001', 'C001', 67);

    Insert Into score(stuid, courseno, scores) Values('1002', 'C001', 68);

    Insert Into score(stuid, courseno, scores) Values('1003', 'C001', 69);

    Insert Into score(stuid, courseno, scores) Values('1004', 'C001', 70);

    Insert Into score(stuid, courseno, scores) Values('1005', 'C001', 71);

    Insert Into score(stuid, courseno, scores) Values('1006', 'C001', 72);

    Insert Into score(stuid, courseno, scores) Values('1001', 'C002', 87);

    Insert Into score(stuid, courseno, scores) Values('1002', 'C002', 88);

    Insert Into score(stuid, courseno, scores) Values('1003', 'C002', 89);

    Insert Into score(stuid, courseno, scores) Values('1004', 'C002', 90);

    Insert Into score(stuid, courseno, scores) Values('1005', 'C002', 91);

    Insert Into score(stuid, courseno, scores) Values('1006', 'C002', 92);

    Insert Into score(stuid, courseno, scores) Values('1001', 'C003', 83);

    Insert Into score(stuid, courseno, scores) Values('1002', 'C003', 84);

    Insert Into score(stuid, courseno, scores) Values('1003', 'C003', 85);

    Insert Into score(stuid, courseno, scores) Values('1004', 'C003', 86);

    Insert Into score(stuid, courseno, scores) Values('1005', 'C003', 87);

    Insert Into score(stuid, courseno, scores) Values('1006', 'C003', 88);

    Insert Into score(stuid, courseno, scores) Values('1001', 'C004', 88);

    Insert Into score(stuid, courseno, scores) Values('1002', 'C004', 89);

    Insert Into score(stuid, courseno, scores) Values('1003', 'C004', 90);

    Insert Into score(stuid, courseno, scores) Values('1004', 'C004', 91);

    Insert Into score(stuid, courseno, scores) Values('1005', 'C004', 92);

    Insert Into score(stuid, courseno, scores) Values('1006', 'C004', 93);

    Insert Into score(stuid, courseno, scores) Values('1001', 'C005', 77);

    Insert Into score(stuid, courseno, scores) Values('1002', 'C005', 78);

    Insert Into score(stuid, courseno, scores) Values('1003', 'C005', 79);

题目：查询每个学生没门课程的成绩

    -- 查询数据

    select st.stuid, st.stunm from student st

    select sc.stuid, sc.courseno, sc.scores from score sc

    select cs.courseno, cs.coursenm from courses cs

    -- 查询每个学生没门课程的成绩

    -- 方式一；拆分选择

    select

      st.stunm '姓名'

    , sum(case cs.courseno when 'C001' then sc.scores end) '大学语文'

    , sum(case cs.courseno when 'C002' then sc.scores end) '新视野英语'

    , sum(case cs.courseno when 'C003' then sc.scores end) '离散数学'

    , sum(case cs.courseno when 'C004' then sc.scores end) '概率论与数理统计'

    , sum(case cs.courseno when 'C005' then sc.scores end) '线性代数'

    , sum(case cs.courseno when 'C006' then sc.scores end) '高等数学(一)'

    , sum(case cs.courseno when 'C007' then sc.scores end) '高等数学(二)'

    from score sc

      inner join student st on st.stuid = sc.stuid

      inner join courses cs on cs.courseno = sc.courseno

    group by st.stunm

    -- 方式二：使用pivot语法

    select *

    from (

      select st.stunm, cs.coursenm, sc.scores

      from score sc

        inner join student st on st.stuid = sc.stuid

        inner join courses cs on cs.courseno = sc.courseno ) a

    pivot (

      sum(scores) for coursenm

      in(大学语文, 新视野英语, 离散数学, 概率论与数理统计, 线性代数, [高等数学(一)], [高等数学(二)])

    ) pvt

    -- 方式三：动态sql

结果图示：

参考链接：

mysql行转列：https://blog.csdn.net/sinat_27406925/article/details/77507478

SQL Server行转列：https://www.cnblogs.com/no27/p/6398130.html

---------------------

作者：不会撒谎的乌索普

来源：CSDN

原文：https://blog.csdn.net/hncu1306602liuqiang/article/details/82934250

版权声明：本文为博主原创文章，转载请附上博文链接！