共计 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
以上均由网友 liangCK , wwwwb , WWWWA , dap570 提供, 再次感谢他们的支持。
其实数据库中也可以用 CASE WHEN / DECODE 代替 IF
到此,关于“mysql 中的行列转换方法”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注丸趣 TV 网站,丸趣 TV 小编会继续努力为大家带来更多实用的文章!