mysql动态行转列函数 mysql实现行转列

前言

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,'小说','2013-09-01',10000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (2,'微信','2013-09-01',20000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (3,'小说','2013-09-02',30000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (4,'微信','2013-09-02',35000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (5,'小说','2013-09-03',31000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (6,'微信','2013-09-03',36000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (7,'小说','2013-09-04',35000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (8,'微信','2013-09-04',38000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (9,'小说','2013-09-01',80000);
INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (10,'微信','2013-09-01',70000);

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;

二、列转行

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);

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