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

2024-07-24 12:52:55

字体：大中小

来源：转载

供稿：网友

（1）动态，适用于列不确定情况

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

（2）第二个字段确定的情况下使用

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;

注： WITH ROLLUP 用于列上求和； SUM(IF(col2='total',col3,0)) AS total 用于行上求和。

（3）第二个字段确定的情况下使用

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 ;

以上这篇mysql 行列动态转换的实现(列联表,交叉表)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持错新站长站。

上一篇：MySQL 实现树的遍历详解及简单实现示例

下一篇：mysql默认编码为UTF-8 通过修改my.ini实现方法