交叉表 mysql_mysql交叉表查询解决方案整理

交叉表是一种常用的分类汇总查询。使用交叉表查询，可以显示表中某个字段的汇总值，并将它们分组，其中一组列在数据表的左侧，另一组列在数据表的上部。行和列的交叉处可以对数据进行多种汇总计算，如：求和、平均值、记数、最大值、最小值等。使用交叉表查询数据非常直观明了，被广泛应用。交叉表查询也是数据库的一个特点。

以下例子收集自csdn整理

数据样本：

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

-> IFNULL(c1,'total') AStotal,-> SUM(IF(c2='B1',c3,0)) ASB1,-> SUM(IF(c2='B2',c3,0)) ASB2,-> SUM(IF(c2='B3',c3,0)) ASB3,-> SUM(IF(c2='B4',c3,0)) ASB4,-> SUM(IF(c2='total',c3,0)) AStotal-> FROM(-> SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) ASc3-> FROMtx-> GROUP BYc1,c2-> WITHROLLUP-> HAVING c1 IS NOT NULL

-> ) ASA-> GROUP BYc1-> WITHROLLUP;+-------+------+------+------+------+-------+

| 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)

方法二：

mysql> selectc1,-> sum(if(c2='B1',C3,0)) ASB1,-> sum(if(c2='B2',C3,0)) ASB2,-> sum(if(c2='B3',C3,0)) ASB3,-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) ASTOTAL-> fromtx-> group byC1-> UNION

-> SELECT 'TOTAL',sum(if(c2='B1',C3,0)) ASB1,-> sum(if(c2='B2',C3,0)) ASB2,-> sum(if(c2='B3',C3,0)) ASB3,-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROMTX->;+-------+------+------+------+------+-------+

| 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.00sec)

mysql>

方法三：

mysql> select ifnull(c1,'total'),-> sum(if(c2='B1',C3,0)) ASB1,-> sum(if(c2='B2',C3,0)) ASB2,-> sum(if(c2='B3',C3,0)) ASB3,-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) ASTOTAL-> fromtx-> group by C1 withrollup ;+--------------------+------+------+------+------+-------+

| 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.00sec)

mysql>