mysql中怎么實現行列轉換操作

147次閱讀

共計 1222 個字符，預計需要花費 4 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關 mysql 中怎么實現行列轉換操作，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

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;