Home > Database > Mysql Tutorial > mysql行列转换_MySQL

mysql行列转换_MySQL

WBOY
Release: 2016-06-01 13:43:14
Original
1011 people have browsed it

bitsCN.com 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;

作者 脚丫 bitsCN.com

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template