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;

關于mysql中怎么實現行列轉換操作就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。