mysql的max case,MySQL — 行转列 — GROUP_CONCAT — MAX(CASE WHEN THEN)

列转行： 多列转多行

行转列：多行转多列

#

以下转自：https://www.cnblogs.com/ClassNotFoundException/p/6860615.html

列转行：利用max(case when then)

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`

;

合并字段显示:利用group_cancat(course,”:”,”score”)

SELECT

`name`,

GROUP_CONCAT(course, ":", score) AS 成绩

FROM

student

GROUP BY

`name`;

-- — — — — — — — – — — — — — — — — — — — — — — — — — — — — – — — — — — — — — — — — — — — — — — — — — – — — — — — — — — — — — — –

-- 合并字段显示 ： 去重、排序

SELECT rid,GROUP_CONCAT(DISTINCT gid ORDER BY gid)

FROM gt_lighting.res_lights

GROUP BY rid;

-- — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

多来点面试题：

计算各班级及格人数：

SELECT class,SUM(CASE WHEN score >= 60 THEN 1 END) AS '及格',SUM(CASE WHEN score < 60 THEN 1 END) AS '不及格'

FROM gradeTable

GROUP BY class;

或者：

SELECT succ.class,succ.su AS '及格',fail.fa AS '不及格' FROM

(

SELECT class,COUNT(1) AS su

FROM gradeTable

WHERE score >= 60

GROUP BY class

) AS succ,

(

SELECT class,COUNT(1) AS fa

FROM gradeTable

WHERE score < 60

GROUP BY class

) AS fail

WHERE succ.class = fail.class;

Console ：

啦啦啦

https://www.cnblogs.com/maohuidong/tag/Mysql/default.html