mysql中的行列转换方法

97次阅读

共计 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