SQL中进行转列
在很多笔试的程序员中会有很多写SQL的情况，其中很多时候会考察行转列。那么这个时候如果能写出来几种行转列的SQL，会给面试官留下比较好的印象。
以下是这次sql转换的表结构以及数据
数据准备
1、学生表
CREATE TABLE `student` (
`stuid` VARCHAR(16) NOT NULL COMMENT '学号',
`stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名',
PRIMARY KEY (`stuid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
2、课程表
CREATE TABLE `curriculum` (
`courseno` VARCHAR(20) NOT NULL,
`coursenm` VARCHAR(100) NOT NULL,
PRIMARY KEY (`courseno`)
)
COMMENT='课程表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
3、成绩表
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;
4、基本数据
/*学生表数据*/
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 curriculum (courseno, coursenm) Values('C001', '大学语文');
Insert Into curriculum (courseno, coursenm) Values('C002', '新视野英语');
Insert Into curriculum (courseno, coursenm) Values('C003', '离散数学');
Insert Into curriculum (courseno, coursenm) Values('C004', '概率论与数理统计');
Insert Into curriculum (courseno, coursenm) Values('C005', '线性代数');
Insert Into curriculum (courseno, coursenm) Values('C006', '高等数学(一)');
Insert Into curriculum (courseno, coursenm) Values('C007', '高等数学(二)');
/*成绩表数据*/
Insert Into number_result(stuid, courseno, scores) Values('1001', 'C001', 67);
Insert Into number_result(stuid, courseno, scores) Values('1002', 'C001', 68);
Insert Into number_result(stuid, courseno, scores) Values('1003', 'C001', 69);
Insert Into number_result(stuid, courseno, scores) Values('1004', 'C001', 70);
Insert Into number_result(stuid, courseno, scores) Values('1005', 'C001', 71);
Insert Into number_result(stuid, courseno, scores) Values('1006', 'C001', 72);
Insert Into number_result(stuid, courseno, scores) Values('1001', 'C002', 87);
Insert Into number_result(stuid, courseno, scores) Values('1002', 'C002', 88);
Insert Into number_result(stuid, courseno, scores) Values('1003', 'C002', 89);
Insert Into number_result(stuid, courseno, scores) Values('1004', 'C002', 90);
Insert Into number_result(stuid, courseno, scores) Values('1005', 'C002', 91);
Insert Into number_result(stuid, courseno, scores) Values('1006', 'C002', 92);
Insert Into number_result(stuid, courseno, scores) Values('1001', 'C003', 83);
Insert Into number_result(stuid, courseno, scores) Values('1002', 'C003', 84);
Insert Into number_result(stuid, courseno, scores) Values('1003', 'C003', 85);
Insert Into number_result(stuid, courseno, scores) Values('1004', 'C003', 86);
Insert Into number_result(stuid, courseno, scores) Values('1005', 'C003', 87);
Insert Into number_result(stuid, courseno, scores) Values('1006', 'C003', 88);
Insert Into number_result(stuid, courseno, scores) Values('1001', 'C004', 88);
Insert Into number_result(stuid, courseno, scores) Values('1002', 'C004', 89);
Insert Into number_result(stuid, courseno, scores) Values('1003', 'C004', 90);
Insert Into number_result(stuid, courseno, scores) Values('1004', 'C004'