MySQL 面试题：一维表转二维表

https://www.modb.pro/db/75127 案例一是两个表之间的操作 案例二是单表操作

案例一：查询所有学生的课程及分数情况
分析，课程与分数情况，用到学生表及成绩表，两张表都是一维表，将其转化为右图所示的二维表

结果：

第一步：将两张表格连接为一张表格

SELECT stu.*,c_id,score
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id;

第二步，将上面得到的结果转换为二维表

SELECT stu.s_id,stu.s_name,
        SUM(if(c_id='01',score,0)) '01',
        SUM(if(c_id='02',score,0)) '02',
        SUM(if(c_id='03',score,0)) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

SELECT stu.s_id,stu.s_name,
        SUM(CASE WHEN c_id='01' THEN score ELSE 0 END) '01',
        SUM(CASE WHEN c_id='02' THEN score ELSE 0 END) '02',
        SUM(CASE WHEN c_id='03' THEN score ELSE 0 END) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;
SELECT stu.s_id,stu.s_name,
        SUM((c_id='01')*score) '01',
        SUM((c_id='02')*score) '02',
        SUM((c_id='03')*score) '03'
FROM stu 
LEFT JOIN sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id;

案例二：把包含柜号、类型、文件路径的一维表转为二维表，按类型

SELECT
    `柜号` AS `柜号`,
    group_concat(DISTINCT case when `类型`="报关" then `文件路径` else NULL end SEPARATOR '|' ) AS `报关_文件路径`,
    group_concat(DISTINCT case when `类型`="检疫" then `文件路径` else NULL end SEPARATOR '|' ) AS `检疫_文件路径`,
    group_concat(DISTINCT case when `类型`="消杀" then `文件路径` else NULL end SEPARATOR '|' ) AS `消杀_文件路径`,
    group_concat(DISTINCT case when `类型`="核酸" then `文件路径` else NULL end SEPARATOR '|' ) AS `核酸_文件路径`
FROM
    `ccsy_certificate` 
GROUP BY
    `柜号`

案例三
题目：以下是每个学生成绩，我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据，请用sql实现。

CREATE TABLE stu_scores (UserName VARCHAR(20),Subject VARCHAR(30),Score FLOAT);
INSERT INTO stu_scores SELECT 'Nick', '语文', 80;
INSERT INTO stu_scores SELECT 'Nick', '数学', 90;
INSERT INTO stu_scores SELECT 'Nick', '英语', 70;
INSERT INTO stu_scores SELECT 'Nick', '生物', 85;
INSERT INTO stu_scores SELECT 'Kent', '语文', 80;
INSERT INTO stu_scores SELECT 'Kent', '数学', 90;
INSERT INTO stu_scores SELECT 'Kent', '英语', 70;
INSERT INTO stu_scores SELECT 'Kent', '生物', 85;
-- 一维表转二维表
select UserName,sum(if(Subject='语文',Score,0)) '语文',sum(if(Subject='数学',Score,0)) '数学',sum(if(Subject='英语',Score,0)) '英语',sum(if(Subject='生物',Score,0)) '生物' from stu_scores group by UserName;