mysql中的行转列，列转行

前言

最近刷  leetcode 1179 一道数据库算法题，涉及到了一列根据内容拆分多列，以此为基础想到了之前有涉及行转列，列转行，故作简单梳理总结

leetcode 1179 重新格式化部门表

1179. 重新格式化部门表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

SELECT id     , sum(CASE         WHEN month = 'Jan' THEN revenue         ELSE NULL     END) AS "Jan_Revenue"     , sum(CASE         WHEN month = 'Feb' THEN revenue         ELSE NULL     END) AS "Feb_Revenue"     , sum(CASE         WHEN month = 'Mar' THEN revenue         ELSE NULL     END) AS "Mar_Revenue"     , sum(CASE         WHEN month = 'Apr' THEN revenue         ELSE NULL     END) AS "Apr_Revenue"     , sum(CASE         WHEN month = 'May' THEN revenue         ELSE NULL     END) AS "May_Revenue"     , sum(CASE         WHEN month = 'Jun' THEN revenue         ELSE NULL     END) AS "Jun_Revenue"     , sum(CASE         WHEN month = 'Jul' THEN revenue         ELSE NULL     END) AS "Jul_Revenue"     , sum(CASE         WHEN month = 'Aug' THEN revenue         ELSE NULL     END) AS "Aug_Revenue"     , sum(CASE         WHEN month = 'Sep' THEN revenue         ELSE NULL     END) AS "Sep_Revenue"     , sum(CASE         WHEN month = 'Oct' THEN revenue         ELSE NULL     END) AS "Oct_Revenue"     , sum(CASE         WHEN month = 'Nov' THEN revenue         ELSE NULL     END) AS "Nov_Revenue"     , sum(CASE         WHEN month = 'Dec' THEN revenue         ELSE NULL     END) AS "Dec_Revenue" FROM Department GROUP BY id

　　

准备工作

单列  转 多列

?

1 2 3 4 5 6 7

CREATE TABLE `student` (   `ID` int(10) NOT NULL AUTO_INCREMENT,   `USER_NAME` varchar(20) DEFAULT NULL,   `COURSE` varchar(20) DEFAULT NULL,   `SCORE` float DEFAULT '0',   PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;insert into student(USER_NAME, COURSE, SCORE) values ("张三", "数学", 34), ("张三", "语文", 58), ("张三", "英语", 58), ("李四", "数学", 45), ("李四", "语文", 87), ("李四", "英语", 45), ("王五", "数学", 76), ("王五", "语文", 34), ("王五", "英语", 89);

　

 

 

　

SELECT user_name
    , MAX(CASE 
        WHEN course = '数学' THEN SCORE
        ELSE NULL
    END) AS "数学"
    , MAX(CASE 
        WHEN course = '语文' THEN SCORE
        ELSE NULL
    END) AS "语文"
    , MAX(CASE 
        WHEN course = '英语' THEN SCORE
        ELSE NULL
    END) AS "英误"
FROM student
GROUP BY user_name

 

 

CREATE TABLE `grade` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) DEFAULT NULL,
  `CN_SCORE` float DEFAULT NULL,
  `MATH_SCORE` float DEFAULT NULL,
  `EN_SCORE` float DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into grade(USER_NAME, CN_SCORE, MATH_SCORE, EN_SCORE) values
("张三", 34, 58, 58),
("李四", 45, 87, 45),
("王五", 76, 34, 89);

 

 

 

SELECT user_name, '语文' AS course, cn_score AS scoure
FROM grade
UNION
(SELECT user_name, '数学' AS course, math_score AS scoure
FROM grade)
UNION
(SELECT user_name, '英语' AS course, en_score AS scoure
FROM grade)