关于MySQL的行转列的简单应用

sql 脚本

-- 创建表  学生表
CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT '学号', `stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名', PRIMARY KEY (`stuid`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB; -- 课程表 CREATE TABLE `courses` ( `courseno` VARCHAR(20) NOT NULL, `coursenm` VARCHAR(100) NOT NULL, PRIMARY KEY (`courseno`) ) COMMENT='课程表' COLLATE='utf8_general_ci' ENGINE=InnoDB; -- 成绩表 CREATE TABLE `score` ( `stuid` VARCHAR(16) NOT NULL, `courseno` VARCHAR(20) NOT NULL, `scores` FLOAT NULL DEFAULT NULL, PRIMARY KEY (`stuid`, `courseno`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB; -- 插入数据 -- 学生表数据 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