mysql 行列动态转换的实现（列联表,交叉表）

（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）第二个字段确定的情况下使用

SELECTIFNULL（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 col3FROM table_nameGROUP BY col1,col2WITH ROLLUPHAVING 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 行列动态转换的实现（列联表,交叉表）就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。