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,),
(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

显示全文

全部频道

mysql行列转换_MySQL