表结构及数据
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;