mysql语法之case when then与列转行

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

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

其语法如下：

case语句的语法：

转换单个字段时：

case字段

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;

更多相关文章

随机推荐