mysql进阶篇之行列转置|case when
作者:YXN-sql 阅读量:109 发布日期: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