MySQL交叉表实现分享

这篇文章主要为大家详细介绍了MySQL交叉表实现分享，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

数据样本：

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将汇总行标题显示为 Totalmysql> 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交叉表实现分享的内容就先介绍到这里，更多相关文章的可以留意