列转行: 多列转多行
行转列:多行转多列
以下转自: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 :
啦啦啦