行转列，列换行

列转行(分支+聚合)

基础数据

SELECT * FROM test_tb_grade t

查出聚合后的第一个分组数据

SELECT t.*
FROM TEST_TB_GRADE t
GROUP BY t.course

查出每个用户每门课的分数

SELECT t.user_name,
       SUM(IF(t.course,'数学',t.score)) AS 数学,
       SUM(IF(t.course,'英语',t.score)) AS 英语,
       SUM(IF(t.course,'语文',t.score)) AS 语文       
FROM TEST_TB_GRADE t
GROUP BY t.user_name

对各门功课各分数段进行统计 分数段 数学 语文 英语

1. 查出每个用户所处的分数段

SELECT t.*,CASE WHEN t.score<60 THEN '0-60'
                WHEN t.score>60 AND t.score<80 THEN '60-80'
                WHEN t.score>80 AND t.score<100 THEN '80-100'
           END score_gap ##
FROM TEST_TB_GRADE t

2. 查出每门课每个分数段的人数(对课程+分数段进行聚合)

使用case when不用加() 把case when看作一个普通函数，类似date 聚合时不加别名

SELECT t.course,CASE WHEN t.score<=60 THEN '0-60'
                     WHEN t.score>=60 AND t.score<=80 THEN '60-80'
                     WHEN t.score>=80 AND t.score<=100 THEN '80-100'
                END AS gap ,COUNT(*)
FROM TEST_TB_GRADE t
GROUP BY t.course,       
CASE WHEN t.score<=60 THEN '0-60'
     WHEN t.score>=60 AND t.score<=80 THEN '60-80'
     WHEN t.score>=80 AND t.score<=100 THEN '80-100'
END  ##相当于一个字段

通过列转行技巧反转

SELECT t2.gap,SUM(IF(t2.course,'数学',1)) AS 数学,
              SUM(IF(t2.course,'语文',1)) AS 语文,
              SUM(IF(t2.course,'英语',1)) AS 英语
FROM(
SELECT t.course,CASE WHEN t.score<=60 THEN '0-60'
                     WHEN t.score>=60 AND t.score<=80 THEN '60-80'
                     WHEN t.score>=80 AND t.score<=100 THEN '80-100'
                END AS gap ,COUNT(*)
FROM TEST_TB_GRADE t
GROUP BY t.course,       
CASE WHEN t.score<=60 THEN '0-60'
     WHEN t.score>=60 AND t.score<=80 THEN '60-80'
     WHEN t.score>=80 AND t.score<=100 THEN '80-100'
END
)t2 GROUP BY t2.gap 

行转列(union)

基础数据

SELECT * FROM test_tb_grade2 t

实现行专列

实现如下查询效果 user_name,course,score

SELECT user_name,'数学' AS course,match_score AS score FROM test_tb_grade2
UNION SELECT user_name,'语文' AS course,chinese_score AS score FROM test_tb_grade2
UNION SELECT user_name,'英语' AS course,english_score AS score FROM test_tb_grade2
ORDER BY course