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),
(' A2 ', ' B3 ', 8),
(One, ' A3 ', ' B3 ', 8),
(The ' A4 ', ' B3 ', 6),
(A1, ' B4 ', 8),
(' A2 ', ' B4 ', 2),
(' A3 ', ' B4 ', 6),
(A4, ' B4 ', 9),
(A1, ' B4 ', 3),
(A2, ' B4 ', 5),
(' A3 ', ' B4 ', 2),
(The ' 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 |
+----+------+------+------+
Rows in Set (0.00 sec)
Mysql>
Expected results
+------+-----+-----+-----+-----+------+
| C1 | B1 | B2 | B3 | B4 | Total |
+------+-----+-----+-----+-----+------+
| A1 |9 |1 |11 |23 |
| A2 |7 |9 |8 |7 |31 |
| A3 |4 |8 |8 |8 |28 |
| A4 |5 |6 |14 |27 |
| Total |22 |24 |23 |40 |109 |
+------+-----+-----+-----+-----+------+
1. Use SUM (IF ()) to generate columns + with ROLLUP to generate total rows, and to display the total row headings as totals using Ifnull
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
-> has 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 columns + UNION to generate the total row, and use Ifnull to display the total row headings as totals
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. Use SUM (IF ()) to generate columns, directly generate results no longer use 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/TX GROUP by C1 WI TH 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>
Above all by Netizen Liangck, WWWWB, WWWWA, dap570 provide, again thanks their support.
In fact, the database can also use case When/decode instead of IF