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
