mysql 行列动态转换的实现(列联表,交叉表)

create table table_name( id int primary key, col1 char(2), col2 char(2), col3 int);

insert into table_name 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 * from table_name;+----+------+------+------+| id | col1 | col2 | col3 |+----+------+------+------+| 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 |+----+------+------+------+

SET @EE='';SELECT @EE:=CONCAT(@EE,'SUM(IF(col2=/'',col2,'/'',',col3,0)) AS ',col2,',') FROM (SELECT DISTINCT col2 FROM table_name) A;SET @QQ=CONCAT('SELECT ifnull(col1,/'total/') AS columnA,',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(col3) AS TOTAL FROM table_name GROUP BY col1 WITH ROLLUP');PREPARE stmt2 FROM @QQ;EXECUTE stmt2;

+---------+------+------+------+------+-------+| columnA | 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 |+---------+------+------+------+------+-------+

SELECT  IFNULL(col1,'total') AS total,  SUM(IF(col2='B1',col3,0)) AS B1,  SUM(IF(col2='B2',col3,0)) AS B2,  SUM(IF(col2='B3',col3,0)) AS B3,  SUM(IF(col2='B4',col3,0)) AS B4,  SUM(IF(col2='total',col3,0)) AS total FROM (  SELECT col1,IFNULL(col2,'total') AS col2,SUM(col3) AS col3  FROM table_name  GROUP BY col1,col2  WITH ROLLUP  HAVING col1 IS NOT NULL ) AS A GROUP BY col1 WITH ROLLUP;

select ifnull(col1,'total') AS col1, sum(if(col2='B1',col3,0)) AS B1, sum(if(col2='B2',col3,0)) AS B2, sum(if(col2='B3',col3,0)) AS B3, sum(if(col2='B4',col3,0)) AS B4,SUM(col3) AS TOTAL from table_name group by col1 with rollup ;