前言

Oracle中的pivot/unpivot函數(shù)可以很方便的幫助我們實現(xiàn)行列轉(zhuǎn)換，但是MySQL并不支持。
可以在SQL上想辦法，比如pivot考慮用聚合函數(shù)+case when, unpiovt函數(shù)考慮用union all。

一、行轉(zhuǎn)列

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實現(xiàn)

#行轉(zhuǎn)列
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

二、列轉(zhuǎn)行

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, '數(shù)學' AS COURSE, math AS SCORE FROM wd_test2  
UNION SELECT username, '英語' AS COURSE, en AS SCORE FROM wd_test2  
ORDER BY username,COURSE  

圖片.png