mysql left join 行转列_mysql行转列转换

--创建表 学生表

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 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 fromstudent stselect sc.stuid , sc.courseno,sc.scores fromscore scselect cs.courseno,cs.coursenm from courses cs