mysql中怎么实现行列转换操作

开发技术作者：Leah 2024-03-27 06:35:01

这篇文章将为大家详细讲解有关mysql中怎么实现行列转换操作，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解...

这篇文章将为大家详细讲解有关mysql中怎么实现行列转换操作，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

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;