如果你想熟练写各种统计报表的 SQL,那么行转列是你绕不开的一个点,你必须得掌握它。
行转列嘛,就是在原来的数据集上减少行数,增加列的数量。具体是什么情况,大家请往下看。
这里有一张学生成绩表,注意看,小老王 2020 年的成绩缺失了。
name grade point
--------------- ------ --------
小老王 2019 3.6
小老王 2018 4.3
玛丽莲·梦露 2018 3.9
玛丽莲·梦露 2019 4.2
玛丽莲·梦露 2020 4.4
蒜你牛 2020 4.6
蒜你牛 2018 4.0
蒜你牛 2019 4.3
直接看表的数据不能很直观地了解在某个学年里每个学生的成绩,我们希望把每个学年拎出来作为列,就像下面这样子。
name 2018 2019 2020
--------------- ------ ------ --------
小老王 4.3 3.6 (NULL)
玛丽莲梦露 3.9 4.2 4.4
小李子 4.0 4.3 4.6
怎么拎出这几个列的数据呢?可以先试试用 case when
。把学年作为过滤的条件,比如过滤条件是 2018 的时候,只有属于该年度的成绩才能放到 2018
的列中。
SELECT
NAME,
CASE
WHEN grade = 2018
THEN POINT
END AS '2018',
CASE
WHEN grade = 2019
THEN POINT
END AS '2019',
CASE
WHEN grade = 2020
THEN POINT
END AS '2020'
FROM
t
上面的 SQL 执行之后的结果如下:
name 2018 2019 2020
--------------- ------ ------ --------
小老王 (NULL) 3.6 (NULL)
小老王 4.3 (NULL) (NULL)
玛丽莲梦露 3.9 (NULL) (NULL)
玛丽莲梦露 (NULL) 4.2 (NULL)
玛丽莲梦露 (NULL) (NULL) 4.4
小李子 (NULL) (NULL) 4.6
小李子 4.0 (NULL) (NULL)
小李子 (NULL) 4.3 (NULL)
这个结果和我们想要的结果有点接近了,只是每个学生还出现在多行数据中,每个学生应该只对应一行数据才合理。
把多行的数据聚合成一行可以使用聚合函数,max()
、min()
、sum()
在这里都可以使用。因为我们要看到的是每个学生的成绩,所以要将 name
字段作为分组字段。完整的 SQL 如下:
SELECT
NAME,
MAX(
CASE
WHEN grade = 2018
THEN POINT
END) AS '2018',
MAX(
CASE
WHEN grade = 2019
THEN POINT
END) AS '2019',
MAX(
CASE
WHEN grade = 2020
THEN POINT
END) AS '2020'
FROM
t
GROUP BY NAME
写行转列(不包括动态行转列)不难,关键得知道分析哪些字段要作为分组的依据,哪个字段将拆分成多个列。然后,套上下面这个模板就可以实现功能了。
SELECT
分组字段1,
分组字段2,
[ 分组字段n ],
MAX(
CASE
WHEN 条件1成立
THEN 数值对应的字段
END) AS '条件1的列名',
MAX(
CASE
WHEN 条件2成立
THEN 数值对应的字段
END) AS '条件2的列名',
MAX(条件判断n) AS '条件n的列名'
FROM
表
GROUP BY 分组字段1,
分组字段2,
[ 分组字段n ]