mysql中的行转列，列转行

前言

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

leetcode 1179 重新格式化部门表

1179. 重新格式化部门表

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

　　

准备工作

单列  转 多列

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)