mysql中的行列转换方法

49次阅读
没有评论

共计 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 小编会继续努力为大家带来更多实用的文章!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-08-01发表,共计3950字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)