MySQL cross-tabulation has a cross-tabulation in some databases, but it is not available in MySQL. However, many friends on the Internet want to find a solution.
Http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html? 96198
The solution is as follows:
Data sample:
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>
Expected results
+------+-----+-----+-----+-----+------+|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. use SUM (IF () to generate a column + with rollup to generate a summary row, and use IFNULL to display the title of the summary row as 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. use SUM (IF () to generate a column + UNION to generate a summary row, and use IFNULL to display the title of the summary row as 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. generate columns using SUM (IF () and generate results directly without subqueries
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. dynamic, applicable to column uncertainties,
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 preparedmysql> 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>
In fact, the database can also use case when/DECODE to replace IF
The above is the content of MySQL cross tabulation _ MySQL. For more information, see PHP Chinese network (www.php1.cn )!