表结构及数据

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;