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;
结果是这样
但是如果想做成这样的呢？
这就是行转列，也叫作竖表转横表
可以通过 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;
