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

57次阅读

共计 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;