MySQL行列转置

• 数据准备

CREATE TABLE MYTEST(
ID INT(32) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(80) DEFAULT NULL,
TIME varchar(20) DEFAULT NULL,
SCOUNT INT(32),
PRIMARY KEY (ID)
);

INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (1,'ZHANGSN','2019-01-01',10000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (2,'ZHANGSN','2019-02-01',20000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (3,'ZHANGSN','2019-03-01',30000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (4,'ZHANGSN','2019-04-01',40000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (5,'ZHANGSN','2019-05-01',50000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (6,'ZHANGSN','2019-06-01',60000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (7,'ZHANGSN','2019-07-01',70000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (8,'ZHANGSN','2019-08-01',80000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (9,'ZHANGSN','2019-09-01',90000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (10,'ZHANGSN','2019-10-01',100000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (11,'ZHANGSN','2019-11-01',110000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (12,'ZHANGSN','2019-12-01',120000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (13,'LISI','2019-01-01',10000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (14,'LISI','2019-02-01',20000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (15,'LISI','2019-03-01',30000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (16,'LISI','2019-04-01',40000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (17,'LISI','2019-05-01',50000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (18,'LISI','2019-06-01',60000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (19,'LISI','2019-07-01',70000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (20,'LISI','2019-08-01',80000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (21,'LISI','2019-09-01',90000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (22,'LISI','2019-10-01',100000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (23,'LISI','2019-11-01',110000);
INSERT INTO MYTEST (`id`,`NAME`,`TIME`,`SCOUNT`) VALUES (24,'LISI','2019-12-01',120000);

• 查看数据

mysql> SELECT * FROM MYTEST;
+----+---------+------------+--------+
| ID | NAME    | TIME       | SCOUNT |
+----+---------+------------+--------+
|  1 | ZHANGSN | 2019-01-01 |  10000 |
|  2 | ZHANGSN | 2019-02-01 |  20000 |
|  3 | ZHANGSN | 2019-03-01 |  30000 |
|  4 | ZHANGSN | 2019-04-01 |  40000 |
|  5 | ZHANGSN | 2019-05-01 |  50000 |
|  6 | ZHANGSN | 2019-06-01 |  60000 |
|  7 | ZHANGSN | 2019-07-01 |  70000 |
|  8 | ZHANGSN | 2019-08-01 |  80000 |
|  9 | ZHANGSN | 2019-09-01 |  90000 |
| 10 | ZHANGSN | 2019-10-01 | 100000 |
| 11 | ZHANGSN | 2019-11-01 | 110000 |
| 12 | ZHANGSN | 2019-12-01 | 120000 |
| 13 | LISI    | 2019-01-01 |  10000 |
| 14 | LISI    | 2019-02-01 |  20000 |
| 15 | LISI    | 2019-03-01 |  30000 |
| 16 | LISI    | 2019-04-01 |  40000 |
| 17 | LISI    | 2019-05-01 |  50000 |
| 18 | LISI    | 2019-06-01 |  60000 |
| 19 | LISI    | 2019-07-01 |  70000 |
| 20 | LISI    | 2019-08-01 |  80000 |
| 21 | LISI    | 2019-09-01 |  90000 |
| 22 | LISI    | 2019-10-01 | 100000 |
| 23 | LISI    | 2019-11-01 | 110000 |
| 24 | LISI    | 2019-12-01 | 120000 |
+----+---------+------------+--------+
24 rows in set (0.00 sec)

• 列转行:方法一

SELECT NAME,
MAX(CASE TIME WHEN '2019-01-01' THEN SCOUNT ELSE 0 END) AS '01',
MAX(CASE TIME WHEN '2019-02-01' THEN SCOUNT ELSE 0 END) AS '02',
MAX(CASE TIME WHEN '2019-02-01' THEN SCOUNT ELSE 0 END) AS '03',
MAX(CASE TIME WHEN '2019-02-01' THEN SCOUNT ELSE 0 END) AS '04',
MAX(CASE TIME WHEN '2019-01-01' THEN SCOUNT ELSE 0 END) AS '05',
MAX(CASE TIME WHEN '2019-02-01' THEN SCOUNT ELSE 0 END) AS '06',
MAX(CASE TIME WHEN '2019-02-01' THEN SCOUNT ELSE 0 END) AS '07',
MAX(CASE TIME WHEN '2019-02-01' THEN SCOUNT ELSE 0 END) AS '08',
MAX(CASE TIME WHEN '2019-01-01' THEN SCOUNT ELSE 0 END) AS '09',
MAX(CASE TIME WHEN '2019-02-01' THEN SCOUNT ELSE 0 END) AS '10',
MAX(CASE TIME WHEN '2019-02-01' THEN SCOUNT ELSE 0 END) AS '11',
MAX(CASE TIME WHEN '2019-02-01' THEN SCOUNT ELSE 0 END) AS '12'
FROM MYTEST GROUP BY NAME;

• 方法二

SELECT NAME,
MAX(IF (TIME = '2019-01-01', SCOUNT,0)) AS '01',
MAX(IF (TIME = '2019-02-01', SCOUNT,0)) AS '02',
MAX(IF (TIME = '2019-03-01', SCOUNT,0)) AS '03',
MAX(IF (TIME = '2019-04-01', SCOUNT,0)) AS '04',
MAX(IF (TIME = '2019-05-01', SCOUNT,0)) AS '05',
MAX(IF (TIME = '2019-06-01', SCOUNT,0)) AS '06',
MAX(IF (TIME = '2019-07-01', SCOUNT,0)) AS '07',
MAX(IF (TIME = '2019-08-01', SCOUNT,0)) AS '08',
MAX(IF (TIME = '2019-09-01', SCOUNT,0)) AS '09',
MAX(IF (TIME = '2019-10-01', SCOUNT,0)) AS '10',
MAX(IF (TIME = '2019-11-01', SCOUNT,0)) AS '11',
MAX(IF (TIME = '2019-12-01', SCOUNT,0)) AS '12'
FROM MYTEST GROUP BY NAME

• 合并字段显示:GROUP_CONCAT

mysql> SELECT NAME, GROUP_CONCAT('TIME',":",SCOUNT) AS CJ FROM MYTEST GROUP BY NAME;

• 数据准备

CREATE TABLE SCORE(
ID INT(10) NOT NULL AUTO_INCREMENT,
SID VARCHAR(20) NOT NULL ,
CN_SCORE DOUBLE,
MATH_SCORE DOUBLE,
EN_SCORE DOUBLE,
PRIMARY KEY (ID)
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

INSERT INTO SCORE  VALUES (1,'01', 90, 92, 80);
INSERT INTO SCORE  VALUES (2,'02', 92, 94, 86);
INSERT INTO SCORE  VALUES (3,'03', 94, 96, 88);

• 查看数据

mysql> SELECT * FROM SCORE;
+----+-----+----------+------------+----------+
| ID | SID | CN_SCORE | MATH_SCORE | EN_SCORE |
+----+-----+----------+------------+----------+
|  1 | 01  |       90 |         92 |       80 |
|  2 | 02  |       92 |         94 |       86 |
|  3 | 03  |       94 |         96 |       88 |
+----+-----+----------+------------+----------+
3 rows in set (0.00 sec)

• 行转列

SELECT SID, 'CHINESE' AS COURSE, CN_SCORE AS SC FROM SCORE
UNION ALL
SELECT SID, 'MATH' AS COURSE, MATH_SCORE AS SC FROM SCORE
UNION ALL
SELECT SID, 'ENGINE' AS COURSE, EN_SCORE AS SC FROM SCORE
ORDER BY SID