MySQL 动态行转列

2021/5/18 posted in  MySQL
表结构及数据 ```

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- Table structure for Course


DROP TABLE IF EXISTS Course;
CREATE TABLE Course (
CId varchar(10) DEFAULT NULL,
Cname varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Records of Course


BEGIN;
INSERT INTO Course VALUES ('1', '数据库');
INSERT INTO Course VALUES ('3', '编译原理');
INSERT INTO Course VALUES ('4', '计算机网络');
INSERT INTO Course VALUES ('2', '操作系统');
COMMIT;


-- Table structure for SC


DROP TABLE IF EXISTS SC;
CREATE TABLE SC (
SId varchar(10) DEFAULT NULL,
CId varchar(10) DEFAULT NULL,
score decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Records of SC


BEGIN;
INSERT INTO SC VALUES ('2', '1', 80.0);
INSERT INTO SC VALUES ('2', '2', 73.0);
INSERT INTO SC VALUES ('2', '3', 87.0);
INSERT INTO SC VALUES ('2', '4', 68.0);
INSERT INTO SC VALUES ('3', '1', 85.0);
INSERT INTO SC VALUES ('3', '2', 66.0);
INSERT INTO SC VALUES ('3', '3', 66.0);
INSERT INTO SC VALUES ('3', '4', 98.0);
INSERT INTO SC VALUES ('1', '1', 90.0);
INSERT INTO SC VALUES ('1', '2', 55.0);
INSERT INTO SC VALUES ('1', '3', 78.0);
INSERT INTO SC VALUES ('1', '4', 45.0);
INSERT INTO SC VALUES ('4', '1', 70.0);
INSERT INTO SC VALUES ('4', '2', 77.0);
INSERT INTO SC VALUES ('4', '3', 56.0);
INSERT INTO SC VALUES ('4', '4', 82.0);
COMMIT;


-- Table structure for Student


DROP TABLE IF EXISTS Student;
CREATE TABLE Student (
SId varchar(10) DEFAULT NULL,
Sname varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Records of Student


BEGIN;
INSERT INTO Student VALUES ('1', '张三');
INSERT INTO Student VALUES ('2', '李四');
INSERT INTO Student VALUES ('3', '王五');
INSERT INTO Student VALUES ('4', '孙六');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

</details>


查看学生哪门课程多少分数

SELECT a.SId,a.Sname,c.CId,c.Cname,b.score from Student a join SC b on a.SId = b.SId
join Course c on c.CId = b.CId order by a.SId;

结果是这样
![](http://hitol.blog.cdn.updev.cn/16213237716609.jpg)

但是如果想做成这样的呢?
![](http://hitol.blog.cdn.updev.cn/16213239029529.jpg)

这就是行转列,也叫作竖表转横表
可以通过 case when 或者 if

IF 语法:
`IF(expr1,expr2,expr3)`
如果 expr1 为 true,则返回 expr2,否则返回 expr3

静态行转列:

SELECT a.SId,a.Sname,

MAX(IF(c.Cname = '操作系统',b.score,null)) as '操作系统',
MAX(IF(c.Cname = '数据库',b.score,null)) as '数据库',
MAX(IF(c.Cname = '编译原理',b.score,null)) as '编译原理',
MAX(IF(c.Cname = '计算机网络',b.score,null)) as '计算机网络'

from Student a join SC b on a.SId = b.SId
join Course c on c.CId = b.CId
group by a.Sname

在这个场景下,用 MAX 或者 SUM 都可以
但是如果增加了课程,这条 sql 也要同步修改


动态 行转列

MAX(IF(c.Cname = '操作系统',b.score,null)) as '操作系统',
MAX(IF(c.Cname = '数据库',b.score,null)) as '数据库',
MAX(IF(c.Cname = '编译原理',b.score,null)) as '编译原理',
MAX(IF(c.Cname = '计算机网络',b.score,null)) as '计算机网络'

从上面看,这一部分的 sql 语法是一样的,利用 concat 函数动态拼接出来:

SELECT
CONCAT('MAX(IF(c.Cname = ''', c.Cname, ''', a.score, NULL)) AS ''', c.Cname, ''')
FROM
Course c;

这一部分有了,把所有的 sql 语句都拼接起来
但是这个地方使用 concat 不行,concat 只会返回一条结果

SET @sql = '';

SELECT
CONCAT('MAX(IF(c.Cname = ''', c.Cname, ''', a.score, NULL)) AS ''', c.Cname, ''')
FROM
Course c INTO @sql;

SELECT @sql;

需要把 concat 的返回结果拼接到一起,这就需要用到 group_concat 函数。

`PREPARE` 语句准备好一条SQL语句,并分配给这条SQL 语句一个名字供之后调用。
准备好的 SQL 语句通过 `EXECUTE` 命令执行,
最后通过 `DEALLOCATE PREPARE` 命令释放掉

所以最终结果是这样的:

SET @sql = '';

SELECT
GROUP_CONCAT(CONCAT('MAX(IF(c.Cname = ''', c.Cname, ''', a.score, NULL)) AS ''', c.Cname, '''))
FROM
Course c INTO @sql;

SET @sql = CONCAT('Select b.Sname as ''姓名'' , ', @sql, ' From SC a
JOIN Student b ON a.SId = b.SId
JOIN Course c ON a.CId = c.CId
Group by b.Sname');

SELECT
@sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


![](http://hitol.blog.cdn.updev.cn/16213239029529.jpg)