mysql的行列转换
来源:互联网 发布:mac cad 看图软件 编辑:程序博客网 时间:2024/05/18 01:02
数据样本:
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 |
+——+—–+—–+—–+—–+——+
使用case when 语句:
语法:case when 条件 then 符合条件后显示的值 else 不符合条件后显示的值 end
eg: case when month = 1 then money else 0 end
如果month=1则该列为money列,否则为0,针对sum()加条件实行。
SELECT c1 ,SUM(CASE WHEN c2 = ‘B1’ THEN c3 ELSE 0 END) AS B1,
SUM(CASE WHEN c2 = ‘B2’ THEN c3 ELSE 0 END) AS B2,
SUM(CASE WHEN c2 = ‘B3’ THEN c3 ELSE 0 END) AS B3
FROM tx GROUP BY c1 WITH rollup
然后再使用子查询生成total列
SELECT c1 ,B1 ,B2,B3,(B1+B2+B3) AS TOTAL FROM (
SELECT c1 ,SUM(CASE WHEN c2 = ‘B1’ THEN c3 ELSE 0 END) AS B1,
SUM(CASE WHEN c2 = ‘B2’ THEN c3 ELSE 0 END) AS B2,
SUM(CASE WHEN c2 = ‘B3’ THEN c3 ELSE 0 END) AS B3
FROM tx GROUP BY c1 WITH ROLLUP
) AS TEMP
这种方法ORACLE和mysql都适用。
结果:
+——+——+——+——+——-+
| c1 | B1 | B2 | B3 | TOTAL |
+——+——+——+——+——-+
| A1 | 9 | 2 | 1 | 12 |
| A2 | 7 | 9 | 8 | 24 |
| A3 | 4 | 8 | 8 | 20 |
| A4 | 2 | 5 | 6 | 13 |
| NULL | 22 | 24 | 23 | 69 |
+——+——+——+——+——-+
5 rows in set (0.00 sec)
以下是我很久之前在网上找了些资料整理到本地的,没有之前的链接。如果原博主看到,可以联系我,加上引用链接。
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的行列转换
- mysql的行列转换
- 关于MYSQL的行列转换
- MySQL逗号分割字段的行列转换
- Mysql行列转换
- mysql行列转换
- Mysql 行列转换
- mysql行列转换示例
- mysql 行列转换
- mysql 行列转换方式
- Mysql行列转换
- MySQL 行列转换
- Mysql-行列转换
- mysql行列转换
- mysql行列转换
- mysql行列转换
- mysql行列转换
- mysql行列转换方法总结
- 轮播图效果
- 常用加密算法的Java实现总结(二) ——对称加密算法AES
- android手机安全卫士、Kotlin漫画、支付宝动画、沉浸状态栏等源码
- git 很少使用的命令
- java中两个double类型相加抛出异常报错问题的解决方法
- mysql的行列转换
- java 线程相关概念
- 最小生成树-Prim算法和Kruskal算法(转)
- js计算两个经纬度之间的直线距离
- 使用IIS Server Farms搭建应用服务负载均衡
- 自定义控件(3)
- 精确时间
- 分布式和集群的简单理解
- 4.5.2 Intent之复杂数据的传递