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 totalFROM(         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 ,txGROUP BY c1     WITH ROLLUP