前言
Oracle中的pivot/unpivot函数可以很方便的帮助我们实现行列转换，但是MySQL并不支持。
可以在SQL上想办法，比如pivot考虑用聚合函数+case when, unpiovt函数考虑用union all。
一、行转列
1-1、准备
create table wd_test(
id int(32) not null auto_increment,
name varchar(80) default null,
date date default null,
scount int(32),
primary key (id)
);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (1,'小说','-09-01',10000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (2,'微信','-09-01',20000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (3,'小说','-09-02',30000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (4,'微信','-09-02',35000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (5,'小说','-09-03',31000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (6,'微信','-09-03',36000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (7,'小说','-09-04',35000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (8,'微信','-09-04',38000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (9,'小说','-09-01',80000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (10,'微信','-09-01',70000);
图片.png
1-2、SQL实现
#行转列
SELECT
a.date,
SUM(CASE a.name
WHEN '小说' THEN a.scount
ELSE 0
END) 'sum_小说',
MAX(CASE a.name
WHEN '小说' THEN a.scount
ELSE 0
END) 'max_小说',
SUM(CASE a.name
WHEN '微信' THEN a.scount
ELSE 0
END) '微信',
MAX(CASE a.name
WHEN '小说' THEN a.scount
ELSE 0
END) 'max_微信'
FROM
wd_test a
GROUP BY DATE;
图片.png
二、列转行
2-1、准备
CREATE TABLE wd_test2
(
id INT(32) NOT NULL AUTO_INCREMENT,
username VARCHAR(80) DEFAULT NULL,
cn FLOAT,
math FLOAT,
en FLOAT,
PRIMARY KEY (id)
)
INSERT INTO `wd_test2` (`id`,`username`,`cn`,`math`,`en`) VALUES (1,'zhangsan',87,65,75);
INSERT INTO `wd_test2` (`id`,`username`,`cn`,`math`,`en`) VALUES (2,'lisi',78,95,81);
INSERT INTO `wd_test2` (`id`,`username`,`cn`,`math`,`en`) VALUES (3,'wangwu',97,78,91);
INSERT INTO `wd_test2` (`id`,`username`,`cn`,`math`,`en`) VALUES (4,'zhaoliu',80,55,75);
图片.png
2-2、SQL
SELECT username, '语文' AS COURSE , cn AS SCORE FROM wd_test2
UNION SELECT username, '数学' AS COURSE, math AS SCORE FROM wd_test2
UNION SELECT username, '英语' AS COURSE, en AS SCORE FROM wd_test2
ORDER BY username,COURSE
图片.png
