MySql行转列案列(附加个人理解)

1 . 使用场景

我们在项目中,报表(工资计算,月份计算等)之类的会用到行转列

2 .mysql脚本(此脚本我是从网上cope的:https://blog.csdn.net/sinat_27406925/article/details/77507478)

别说我懒,毕竟想偷懒你才会想办法比如饿了么

-- 创建表  学生表
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);

3 . 第一种方案

我们一步一步来:

先都联查起来看看结果:

select a.stuid,stunm,coursenm,scores from 
score a left JOIN student s on a.stuid=s.stuid 
LEFT JOIN courses c on a.courseno=c.courseno

;

结果:

看到这结果你会不会想到分组?

select a.stuid,stunm,coursenm,scores from 
score a left JOIN student s on a.stuid=s.stuid 
LEFT JOIN courses c on a.courseno=c.courseno
GROUP BY a.stuid

;

分组后结果:是不是感觉前两列已经出来我们想要的了?那就接着写第三列就是了

第三列怎么查???

select * from 
score a1 
LEFT JOIN courses c1 on a1.courseno=c1.courseno
 where c1.coursenm='大学语文' 
GROUP BY a1.stuid

结果:

现在联起来不就是了???

最终sql:

select a.stuid ,stunm as '姓名',
IFNULL(
(
select a1.scores from 
score a1 
LEFT JOIN courses c1 on a1.courseno=c1.courseno
 where c1.coursenm='大学语文'  and a.stuid=a1.stuid
GROUP BY a1.stuid
)
,0) as '大学语文',
IFNULL(
(
select a2.scores from 
score a2 
LEFT JOIN courses c2 on a2.courseno=c2.courseno
 where c2.coursenm='新视野英语' and a.stuid=a2.stuid
GROUP BY a2.stuid
)
,0) as '新视野英语',
IFNULL(
(
select a3.scores from 
score a3 
LEFT JOIN courses c3 on a3.courseno=c3.courseno
 where c3.coursenm='离散数学' and a.stuid=a3.stuid
GROUP BY a3.stuid
)
,0) as '离散数学',
IFNULL(
(
select a4.scores from 
score a4 
LEFT JOIN courses c4 on a4.courseno=c4.courseno
 where c4.coursenm='概率论与数理统计' and a.stuid=a4.stuid
GROUP BY a4.stuid
)
,0) as '概率论与数理统计',
IFNULL(
(select a5.scores  from 
score a5 
LEFT JOIN courses c5 on a5.courseno=c5.courseno
 where c5.coursenm='线性代数' and a.stuid=a5.stuid
GROUP BY a5.stuid
)
,0) as '线性代数'
from score a 
left JOIN student s on a.stuid=s.stuid 
LEFT JOIN courses c on a.courseno=c.courseno
GROUP BY stuid

注释:IFNULL(参数1,参数2) ,如果参数1为null  就取参数2的值  ,如果参数1不为null  就取参数1的值 

4 . 第二种方案

select a.stuid as '学号',s.stunm as '姓名',
MAX(case c.coursenm when '大学语文' then IFNULL(a.scores,0) else 0 end) as '大学语文',
MAX(case c.coursenm when '新视野英语' then IFNULL(a.scores,0) else 0 end) as '新视野英语',
MAX(case c.coursenm when '离散数学' then IFNULL(a.scores,0) else 0 end) as '离散数学',
MAX(case c.coursenm when '概率论与数理统计' then IFNULL(a.scores,0) else 0 end) as '概率论与数理统计',
MAX(case c.coursenm when '线性代数' then IFNULL(a.scores,0) else 0 end) as '线性代数'
from score a 
left JOIN student s on a.stuid=s.stuid 
LEFT JOIN courses c on a.courseno=c.courseno

GROUP BY a.stuid

结果:

个人理解:

先上两张图(没分组和分组)

是不是发现大学语文都是对的呢 ? 当是我们怎么做呢? 所以加了条件判断啊....

那又为什么加了Max()??  因为你要保证他是只有一条啊,方正课程都只有一个max那也只有那一个啊

新手勿喷 !好了这里都没考虑效率问题.....如果你有更好的解决方案..请评论