mysql case when 行转列_mysql语法之case when then与列转行

mysql语法中case when then与列转行的使用场景非常丰富。

case语句类似java中条件分支语句的作用，可以类比java中的switch语句或者if语句来学习。

其语法如下：

case语句的语法：

转换单个字段时：

case 字段

when 字段值 then 返回值

when 字段值 then 返回值

when 字段值 then 返回值

。。。。。。

else 默认值 end 别名；

当有多个字段需要转换时：

case

when 字段名1=字段值11 and字段名2=字段值2 and。。。then返回值

when 字段名1=字段值12 and字段名2=字段值3 and。。。then返回值

when 字段名1=字段值13 and字段名2=字段值4 and。。。then返回值

when 字段名1=字段值14 and字段名2=字段值5 and。。。then返回值

。。。。。。

else 默认值 end 别名；

而列转行则能够帮我们把数据库某些列转换为行的形式展示给我们。

例如：

将上述结果进行列转行可以得到如下结果：

列转行语法：

sum(case when then else end) as 或者max(case when then else end) as

创建一个数据库stu，建立学生表，班级表，课程表，成绩表

班级表包含班级id、班级名称

创建班级表：

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `classes`;

CREATE TABLE `classes` (

`cid` int(11) NOT NULL,

`cname` varchar(10) DEFAULT NULL,

PRIMARY KEY (`cid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

向班级表插入一个班级

INSERT INTO `classes` VALUES ('111', '一班');

课程表包含课程id、课程名称

创建课程表：

DROP TABLE IF EXISTS `sc`;

CREATE TABLE `sc` (

`scid` int(11) NOT NULL,

`scname` varchar(10) DEFAULT NULL,

PRIMARY KEY (`scid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

向课程表插入三门课程

INSERT INTO `sc` VALUES ('11', '语文');

INSERT INTO `sc` VALUES ('22', '数学');

INSERT INTO `sc` VALUES ('33', '英语');

成绩表包含成绩id、分数、分数所属学生id、分数所属课程id

创建成绩表：

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (

`coid` int(11) NOT NULL,

`score` int(8) DEFAULT NULL,

`sid` int(11) DEFAULT NULL,

`scid` int(11) DEFAULT NULL,

PRIMARY KEY (`coid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

向成绩表插入6条记录：

INSERT INTO `score` VALUES ('1111', '90', '1', '11');

INSERT INTO `score` VALUES ('2222', '99', '1', '22');

INSERT INTO `score` VALUES ('3333', '89', '2', '11');

INSERT INTO `score` VALUES ('4444', '88', '2', '33');

INSERT INTO `score` VALUES ('5555', '75', '3', '22');

INSERT INTO `score` VALUES ('6666', '59', '3', '33');

学生表包含学生id、学生姓名、所属班级id

创建学生表：

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`sid` int(11) NOT NULL,

`sname` varchar(10) DEFAULT NULL,

`cid` int(11) DEFAULT NULL,

PRIMARY KEY (`sid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

向学生表插入三个学生：

INSERT INTO `student` VALUES ('1', '张三', '111');

INSERT INTO `student` VALUES ('2', '李四', '111');

INSERT INTO `student` VALUES ('3', '王五', '111');

查询每个学生的姓名、所属班级、所学课程、课程得分

SELECT

s.sname,

cl.cname,

sc.scname,

co.score

FROM

student s,

classes cl,

sc,

score co

WHERE

s.cid = cl.cid

AND s.sid = co.sid

AND sc.scid = co.scid;

在上一个结果集的基础上，将同一个学生的所有所学课程与所有分数分别以逗号形式连接成字符串：

SELECT

s.sname,

cl.cname,

GROUP_CONCAT(co.score) 分数,

GROUP_CONCAT(sc.scname) 课程

FROM

student s,

classes cl,

sc,

score co

WHERE

s.cid = cl.cid

AND s.sid = co.sid

AND sc.scid = co.scid

GROUP BY

s.sname,

cl.cname;

列转行实例：

①

SELECT

s.sname,

cl.cname,

IFNULL(

Max(

CASE sc.scname

WHEN '语文' THEN

co.score

END

),

'未选'

) AS '语文',

IFNULL(

Max(

CASE sc.scname

WHEN '数学' THEN

co.score

END

),

'未选'

) AS '数学',

IFNULL(

Max(

CASE sc.scname

WHEN '英语' THEN

co.score

END

),

'未选'

) AS '英语'

FROM

student s,

classes cl,

sc,

score co

WHERE

s.cid = cl.cid

AND s.sid = co.sid

AND sc.scid = co.scid

GROUP BY

s.sname,

cl.cname;

②

SELECT

s.sname,

cl.cname,

max(

IF (

sc.scname = '语文',

co.score ,- 1

)

) AS '语文',

max(

IF (

sc.scname = '数学',

co.score ,- 1

)

) AS '数学',

max(

IF (

sc.scname = '英语',

co.score ,- 1

)

) AS '英语'

FROM

student s,

classes cl,

sc,

score co

WHERE

s.cid = cl.cid

AND s.sid = co.sid

AND sc.scid = co.scid

GROUP BY

s.sname,

cl.cname;