SQL 行列置换

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

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 , tx GROUP BY c1 WITH ROLLUP