手机
当前位置:查字典教程网 >编程开发 >mysql数据库 >MySQL交叉表实现分享
MySQL交叉表实现分享
摘要:现整理解法如下:数据样本:createtabletx(idintprimarykey,c1char(2),c2char(2),c3int);...

现整理解法如下:

数据样本:

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交叉表实现分享】相关文章:

MySQL 管理

查询实现删除

让MySQL支持中文排序的实现方法

MySQL中的if和case语句使用总结

MySQL数据库存储引擎和分支现状分析

MySQL 在windows上的安装详细介绍

Mysql 建库建表技巧分享

Linux下MySQL 5.5.8 源码编译安装记录分享

MySQL中GROUP BY的基本实现原理

Centos5.5中安装Mysql5.5过程分享

精品推荐
分类导航