前言

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

图片.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