SQL行列转换面试题

001将每个userid对应的多个科目的成绩查出来，通过UNION ALL将结果集加起来。

变成

select userId,'语文' as subjectName,chinese_score as score from tb_score1
union all
select userId,'数学' as subjectName,math_score as score from tb_score1
union all
select userId,'英语' as subjectName,english_score as score from tb_score1
union all
select userId,'政治' as subjectName,politics_score as score from tb_score1;

002使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称。

-- 考察case表达式
select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when 成绩 >=70 and 成<85 then 1 else 0 end) as '[85-70]',
sum(case when 成绩>=60 and 成绩<70 then 1 else 0 end) as '[70-60]',
sum(case when 成绩<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b 
on a.课程号=b.课程号 group by a.课程号,b.课程名称;

003查询出每门课程的及格人数和不及格人数。

-- 考察case表达式
select 课程号,
sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,
sum(case when 成绩 <  60 then 1 else 0 end) as 不及格人数
from score group by 课程号;

004使用case…when…then 实现原本同一列下多行的不同内容作为多个字段，输出对应内容。

转化后

SELECT
	userid,
	SUM( CASE subjectName WHEN '语文' THEN score ELSE 0 END ) '语文',
	SUM( CASE subjectName WHEN '数学' THEN score ELSE 0 END ) '数学',
	SUM( CASE subjectName WHEN '英语' THEN score ELSE 0 END ) '英语',
	SUM( CASE subjectName WHEN '政治' THEN score ELSE 0 END ) '政治' 
FROM
	tb_score 
GROUP BY
	userid;

005使用SUM(IF()) 实现原本同一列下多行的不同内容作为多个字段，输出对应内容。

SELECT
	userid,
	SUM( IF ( subjectName = '语文', score, 0 ) ) '语文',
	SUM( IF ( subjectName = '数学', score, 0 ) ) '数学',
	SUM( IF ( subjectName = '英语', score, 0 ) ) '英语',
	SUM( IF ( subjectName = '政治', score, 0 ) ) '政治' 
FROM
	tb_score 
GROUP BY
	userid;

006使用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行 。

MySQL提供了 group by with rollup 函数进行group by 字段的汇总，但是与order by 互斥的不能同时用。

SELECT
	IFNULL( userid, 'total' ) AS userid,
	SUM( IF ( subjectName = '语文', score, 0 ) ) '语文',
	SUM( IF ( subjectName = '数学', score, 0 ) ) '数学',
	SUM( IF ( subjectName = '英语', score, 0 ) ) '英语',
	SUM( IF ( subjectName = '政治', score, 0 ) ) '政治',
	SUM( IF ( subjectName = 'total', score, 0 ) ) AS 'total' 
FROM
	(
		SELECT
			userid,
			IFNULL( subjectName, 'total' ) AS subjectName,
			SUM( score ) AS score 
		FROM
			tb_score 
		GROUP BY userid, subjectName WITH ROLLUP 
	) AS a
GROUP BY userid
WITH ROLLUP;

007使用SUM(IF()) 生成列，直接生成汇总结果，不再利用子查询。

SELECT IFNULL(userid,'total') AS userid,
SUM(IF(subjectName='语文',score,0)) AS '语文',
SUM(IF(subjectName='数学',score,0)) AS '数学',
SUM(IF(subjectName='英语',score,0)) AS '英语',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) AS total
FROM tb_score
GROUP BY userid WITH ROLLUP;

008使用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total。

SELECT userid,
SUM(IF(subjectName='语文',score,0)) AS '语文',
SUM(IF(subjectName='数学',score,0)) AS '数学',
SUM(IF(subjectName='英语',score,0)) AS '英语',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) AS total
FROM tb_score
GROUP BY userid
UNION
SELECT 'total',SUM(IF(subjectName='语文',score,0)) AS '语文',
SUM(IF(subjectName='数学',score,0)) AS '数学',
SUM(IF(subjectName='英语',score,0)) AS '英语',
SUM(IF(subjectName='政治',score,0)) AS '政治',
SUM(score) FROM tb_score;

009group_concat()合并字段显示如下内容。

SELECT userid,GROUP_CONCAT(`subjectName`,":",score)AS 成绩 FROM tb_score GROUP BY userid;