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;

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

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;

结果是这样

但是如果想做成这样的呢?

这就是行转列,也叫作竖表转横表
可以通过 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;