这篇文章将为大家详细讲解有关Mysql中怎么实现行列转换操作,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。create table tx(id int pr
这篇文章将为大家详细讲解有关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中怎么实现行列转换操作就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
--结束END--
本文标题: mysql中怎么实现行列转换操作
本文链接: https://www.lsjlt.com/news/58475.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0