In some databases, there are cross tables, but this function 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
-> 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) ', c2, ',') from (select distinct C2 from Tx);
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>
In addition:
If you are testing in a query in navicat for maysql, enter:
Set @ EE = '';
Select @ EE: = Concat (@ EE, 'sum (if (C2 = \ '', C2, '\', C3, 0) as ', C2 ,', ') from (select distinct C2 from Tx);
Set @ qq = Concat ('select ifnull (C1, \ 'total \ '),', left (@ EE, length (@ ee)-1 ),', sum (C3) as total from TX group by C1 with rollup ');
Prepare stmt2 from @ qq;
Execute stmt2
All of the above are by netizensLiangck, wwwwb, wwwwa, dap570Yes. Thanks again for their support.
In fact, the database can also use case when/decode to replace if