mysql行列转换_MySQL
程序员文章站
2022-05-24 16:04:32
...
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
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