mysql中的行列轉換方法

124次閱讀

共計 3950 個字符，預計需要花費 10 分鐘才能閱讀完成。

這篇文章主要介紹“mysql 中的行列轉換方法”，在日常操作中，相信很多人在 mysql 中的行列轉換方法問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”mysql 中的行列轉換方法”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

現整理解法如下：
數據樣本：
create table tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);
insert into tx values
(1 , A1 , B1 ,9),
(2 , A2 , B1 ,7),
(3 , A3 , B1 ,4),
(4 , A4 , B1 ,2),
(5 , A1 , B2 ,2),
(6 , A2 , B2 ,9),
(7 , A3 , B2 ,8),
(8 , A4 , B2 ,5),
(9 , A1 , B3 ,1),
(10 , A2 , B3 ,8),
(11 , A3 , B3 ,8),
(12 , A4 , B3 ,6),
(13 , A1 , B4 ,8),
(14 , A2 , B4 ,2),
(15 , A3 , B4 ,6),
(16 , A4 , B4 ,9),
(17 , A1 , B4 ,3),
(18 , A2 , B4 ,5),
(19 , A3 , B4 ,2),
(20 , A4 , B4 ,5);

mysql select * from tx;
+—-+——+——+——+
| id | c1 | c2 | c3 |
+—-+——+——+——+
| 1 | A1 | B1 | 9 |
| 2 | A2 | B1 | 7 |
| 3 | A3 | B1 | 4 |
| 4 | A4 | B1 | 2 |
| 5 | A1 | B2 | 2 |
| 6 | A2 | B2 | 9 |
| 7 | A3 | B2 | 8 |
| 8 | A4 | B2 | 5 |
| 9 | A1 | B3 | 1 |
| 10 | A2 | B3 | 8 |
| 11 | A3 | B3 | 8 |
| 12 | A4 | B3 | 6 |
| 13 | A1 | B4 | 8 |
| 14 | A2 | B4 | 2 |
| 15 | A3 | B4 | 6 |
| 16 | A4 | B4 | 9 |
| 17 | A1 | B4 | 3 |
| 18 | A2 | B4 | 5 |
| 19 | A3 | B4 | 2 |
| 20 | A4 | B4 | 5 |
+—-+——+——+——+
20 rows in set (0.00 sec)
mysql
期望結果
+——+—–+—–+—–+—–+——+
|C1 |B1 |B2 |B3 |B4 |Total |
+——+—–+—–+—–+—–+——+
|A1 |9 |2 |1 |11 |23 |
|A2 |7 |9 |8 |7 |31 |
|A3 |4 |8 |8 |8 |28 |
|A4 |2 |5 |6 |14 |27 |
|Total |22 |24 |23 |40 |109 |
+——+—–+—–+—–+—–+——+
1. 利用 SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行, 并利用 IFNULL 將匯總行標題顯示為 Total
mysql SELECT
– IFNULL(c1, total) AS total,
– SUM(IF(c2= B1 ,c3,0)) AS B1,
– SUM(IF(c2= B2 ,c3,0)) AS B2,
– SUM(IF(c2= B3 ,c3,0)) AS B3,
– SUM(IF(c2= B4 ,c3,0)) AS B4,
– SUM(IF(c2= total ,c3,0)) AS total
– FROM (
– SELECT c1,IFNULL(c2, total) AS c2,SUM(c3) AS c3
– FROM tx
– GROUP BY c1,c2
– WITH ROLLUP
– HAVING c1 IS NOT NULL
– ) AS A
– GROUP BY c1
– WITH ROLLUP;
+——-+——+——+——+——+——-+
| total | B1 | B2 | B3 | B4 | total |
+——-+——+——+——+——+——-+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+——-+——+——+——+——+——-+
5 rows in set, 1 warning (0.00 sec)
2. 利用 SUM(IF()) 生成列 + UNION 生成匯總行, 并利用 IFNULL 將匯總行標題顯示為 Total
mysql select c1,
– sum(if(c2= B1 ,C3,0)) AS B1,
– sum(if(c2= B2 ,C3,0)) AS B2,
– sum(if(c2= B3 ,C3,0)) AS B3,
– sum(if(c2= B4 ,C3,0)) AS B4,SUM(C3) AS TOTAL
– from tx
– group by C1
– UNION
– SELECT TOTAL ,sum(if(c2= B1 ,C3,0)) AS B1,
– sum(if(c2= B2 ,C3,0)) AS B2,
– sum(if(c2= B3 ,C3,0)) AS B3,
– sum(if(c2= B4 ,C3,0)) AS B4,SUM(C3) FROM TX
–
+——-+——+——+——+——+——-+
| c1 | B1 | B2 | B3 | B4 | TOTAL |
+——-+——+——+——+——+——-+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| TOTAL | 22 | 24 | 23 | 40 | 109 |
+——-+——+——+——+——+——-+
5 rows in set (0.00 sec)
mysql

3. 利用 SUM(IF()) 生成列, 直接生成結果不再利用子查詢
mysql select ifnull(c1, total),
– sum(if(c2= B1 ,C3,0)) AS B1,
– sum(if(c2= B2 ,C3,0)) AS B2,
– sum(if(c2= B3 ,C3,0)) AS B3,
– sum(if(c2= B4 ,C3,0)) AS B4,SUM(C3) AS TOTAL
– from tx
– group by C1 with rollup ;
+——————–+——+——+——+——+——-+
| ifnull(c1, total) | B1 | B2 | B3 | B4 | TOTAL |
+——————–+——+——+——+——+——-+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+——————–+——+——+——+——+——-+
5 rows in set (0.00 sec)
mysql

4. 動態，適用于列不確定情況，
mysql SET @EE=
mysql SELECT @EE:=CONCAT(@EE, SUM(IF(C2=\ ,C2, \ , ,C3,0)) AS ,C2, , ) FROM (SELECT DISTINCT C2 FROM TX) A;

mysql SET @QQ=CONCAT(SELECT ifnull(c1,\ total\), ,LEFT(@EE,LENGTH(@EE)-1), ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP
Query OK, 0 rows affected (0.00 sec)
mysql PREPARE stmt2 FROM @QQ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql EXECUTE stmt2;
+——————–+——+——+——+——+——-+
| ifnull(c1, total) | B1 | B2 | B3 | B4 | TOTAL |
+——————–+——+——+——+——+——-+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+——————–+——+——+——+——+——-+
5 rows in set (0.00 sec)
mysql