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示例),mysql示例

这篇文章通过sql示例代码给大家介绍了mysql数据库如何实现行列转换,下面话不多说,直接来看示例代码吧。

原表:

表名 :user
----------------------------------------
name    | course  | grade
----------------------------------------
zhangsan  | Java   | 70
----------------------------------------
zhangsan  | C++   | 80
----------------------------------------
lisi    | java   | 90
----------------------------------------
lisi    | C#    | 60
----------------------------------------

用一条 SQL 语句得到如下形式:

----------------------------------------
name   | java | C++ | C#
----------------------------------------
zhangsan | 70  | 80  | null
----------------------------------------
lisi   | 90  | null | 60
----------------------------------------

方案一

select  name,
sum(case when course='java' then grade end) as java,
sum(case when course='C++' then grade end) as C++,
sum(case when course='C#' then grade end) as C#
from test group by name

方案二

select distinct c.`name` AS name,
(select grade from test where name = c.`name` and course = 'java' )as java,
(select grade from test where name = c.`name` and course = 'C++' )as C++,
(select grade from test where name = c.`name` and course = 'C#' )as C#
from test c

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能有一定的帮助,如果有疑问大家可以留言交流。

这篇文章通过sql示例代码给大家介绍了mysql数据库如何实现行列转换,下面话不多说,直接来看示…

作者 脚丫

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图