mysql行列转换_MySQL

bitsCN.com create table tx(
id int primary key,
c1 char(2),
c2 char(2),
money int,
num int
);



insert into tx values
(1 ,'A1','B1',9,81),
(2 ,'A2','B1',7,53),
(3 ,'A3','B1',4,62),
(4 ,'A4','B1',2,91),
(5 ,'A1','B2',2,42),
(6 ,'A2','B2',9,66),
(7 ,'A3','B2',8,84),
(8 ,'A4','B2',5,55),
(9 ,'A1','B3',1,61),
(10 ,'A2','B3',8,43),
(11 ,'A3','B3',8,64),
(12 ,'A4','B3',6,72),
(13 ,'A1','B4',8,33),
(14 ,'A2','B4',2,24),
(15 ,'A3','B4',6,76),
(16 ,'A4','B4',9,51),
(17 ,'A1','B4',3,30),
(18 ,'A2','B4',5,26),
(19 ,'A3','B4',2,15),
(20 ,'A4','B4',5,11);

/*第一种静态列*/
select ifnull(c1,'total'),
sum(if(c2='B1',money,0)) AS B1money,
sum(if(c2='B1',num,0)) AS B1num,
sum(if(c2='B2',money,0)) AS B2money,
sum(if(c2='B2',num,0)) AS B2num,
sum(if(c2='B3',money,0)) AS B3money,
sum(if(c2='B3',num,0)) AS B3num,
sum(if(c2='B4',money,0)) AS B4money,
sum(if(c2='B4',num,0)) AS B4num,
SUM(money) AS TOTAL,
SUM(num) AS TOTAL
from tx
group by c1 with rollup ;



/*第二种动态列*/

SET @EE='';

SELECT @EE:=CONCAT(
@EE,
'SUM(IF(C2=/'',C2,'/'',
',money,0)) AS ',
C2,
'money,',
'SUM(IF(C2=/'',C2,'/'',
',num,0)) AS ',
C2,
'num,') FROM (SELECT DISTINCT C2 FROM TX) A;

SET @QQ=CONCAT('SELECT ifnull(c1,/'total/'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(money) AS moneyTOTAL,SUM(num) AS numTOTAL FROM

TX GROUP BY C1 WITH ROLLUP');

PREPARE stmt2 FROM @QQ;

EXECUTE stmt2;

作者 脚丫 bitsCN.com