mysql进阶篇之行列转置|case when

作者：YXN-sql 阅读量：118 发布日期：2022-10-01

1、成绩统计

有此数据表：

行列转置

-- 使用CASE WHEN THEN ELSE END
SELECT
	`name`,
	MAX( CASE WHEN course = '语文' THEN score END ) AS 语文,
	MAX( CASE WHEN course = '数学' THEN score END ) AS 数学,
	MAX( CASE WHEN course = '英语' THEN score END ) AS 英语 
FROM
	student 
GROUP BY
	`name`;
	
-- 使用if
SELECT
	'name',
	MAX(IF( course = '语文', score, 0 )) AS 语文,
	MAX(IF( course = '数学', score, 0 )) AS 数学,
	MAX(IF( course = '英语', score, 0 )) AS 英语 
FROM
	student 
GROUP BY
	`name`;

得到如下结果：

字段合并

SELECT
	`name`,
	GROUP_CONCAT( course, ":", score ) AS 成绩 
FROM
	student 
GROUP BY
	`name`;

得到如下结果：

2、课程统计

数据表：

思路：先找出上午的课和下午的课组成一个表，再通过case when进行行列转置

SELECT
	k_time
	,GROUP_CONCAT(case when k_day='周一' and ifyouke='有课' THEN k_name ELSE NULL END) as 周一
	,GROUP_CONCAT(case when k_day='周二' and ifyouke='有课' THEN k_name ELSE NULL END) as 周二
	,GROUP_CONCAT(case when k_day='周三' and ifyouke='有课' THEN k_name ELSE NULL END) as 周三
	,GROUP_CONCAT(case when k_day='周四' and ifyouke='有课' THEN k_name ELSE NULL END) as 周四
	,GROUP_CONCAT(case when k_day='周五' and ifyouke='有课' THEN k_name ELSE NULL END) as 周五
FROM 
(
	SELECT k_day, k_name, morning as ifyouke,  '上午' as `k_time` FROM case_when
	UNION all
	SELECT k_day, k_name, afternoon as ifyouke,  '下午' as `k_time` FROM case_when 
) as t
GROUP BY k_time

YXN-sql

2022-10-01