mysql数据库列编程行,代码区软件项目交易网,CodeSection,代码区,mysql行列转换方法总结 mysql mysql数据库 mysql函数 行列转换方法...

mysql行列转换方法总结

数据样本：create table tx(

id int primary key auto_increment,

c1 char(2),

c2 char(2),

c3 int

);

insert into tx(c1,c2,c3) values

('A1','B1',9),

('A2','B1',7),

('A3','B1',4),

('A4','B1',2),

('A1','B2',2),

('A2','B2',9),

('A3','B2',8),

('A4','B2',5),

('A1','B3',1),

('A2','B3',8),

('A3','B3',8),

('A4','B3',6),

('A1','B4',8),

('A2','B4',2),

('A3','B4',6),

('A4','B4',9),

('A1','B4',3),

('A2','B4',5),

('A3','B4',2),

('A4','B4',5);

mysql> select * from tx;

+----+------+------+------+

| id | c1 | c2 | c3 |

+----+------+------+------+

| 1 | A1 | B1 | 9 |

| 2 | A2 | B1 | 7 |

| 3 | A3 | B1 | 4 |

| 4 | A4 | B1 | 2 |

| 5 | A1 | B2 | 2 |

| 6 | A2 | B2 | 9 |

| 7 | A3 | B2 | 8 |

| 8 | A4 | B2 | 5 |

| 9 | A1 | B3 | 1 |

| 10 | A2 | B3 | 8 |

| 11 | A3 | B3 | 8 |

| 12 | A4 | B3 | 6 |

| 13 | A1 | B4 | 8 |

| 14 | A2 | B4 | 2 |

| 15 | A3 | B4 | 6 |

| 16 | A4 | B4 | 9 |

| 17 | A1 | B4 | 3 |

| 18 | A2 | B4 | 5 |

| 19 | A3 | B4 | 2 |

| 20 | A4 | B4 | 5 |

+----+------+------+------+

20 rows in set (0.00 sec)

mysql>

期望结果+------+-----+-----+-----+-----+------+

|C1 |B1 |B2 |B3 |B4 |Total |

+------+-----+-----+-----+-----+------+

|A1 |9 |2 |1 |11 |23 |

|A2 |7 |9 |8 |7 |31 |

|A3 |4 |8 |8 |8 |28 |

|A4 |2 |5 |6 |14 |27 |

|Total |22 |24 |23 |40 |109 |

+------+-----+-----+-----+-----+------+

1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

mysql>SELECT

IFNULL(c1,'total') AS total,

SUM(IF(c2='B1',c3,0)) AS B1,

SUM(IF(c2='B2',c3,0)) AS B2,

SUM(IF(c2='B3',c3,0)) AS B3,

SUM(IF(c2='B4',c3,0)) AS B4,

SUM(IF(c2='total',c3,0)) AS total

FROM (

SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3

FROM tx GROUP BY c1,c2

WITH ROLLUP

HAVING c1 IS NOT NULL

) AS A

GROUP BY c1

WITH ROLLUP;

+-------+------+------+------+------+-------+

| total | B1 | B2 | B3 | B4 | total |

+-------+------+------+------+------+-------+

| A1 | 9 | 2 | 1 | 11 | 23 |

| A2 | 7 | 9 | 8 | 7 | 31 |

| A3 | 4 | 8 | 8 | 8 | 28 |

| A4 | 2 | 5 | 6 | 14 | 27 |

| total | 22 | 24 | 23 | 40 | 109 |

+-------+------+------+------+------+-------+

5 rows in set, 1 warning (0.00 sec)

/*

select c1,c2,sum(c3) from tx group by c1,c2

*/2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

mysql>

select c1,

sum(if(c2='B1',C3,0)) AS B1,

sum(if(c2='B2',C3,0)) AS B2,

sum(if(c2='B3',C3,0)) AS B3,

sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL

from tx

group by C1

UNION

SELECT 'TOTAL',sum(if(c2='B1',C3,0)) AS B1,

sum(if(c2='B2',C3,0)) AS B2,

sum(if(c2='B3',C3,0)) AS B3,

sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX;

+-------+------+------+------+------+-------+

| c1 | B1 | B2 | B3 | B4 | TOTAL |

+-------+------+------+------+------+-------+

| A1 | 9 | 2 | 1 | 11 | 23 |

| A2 | 7 | 9 | 8 | 7 | 31 |

| A3 | 4 | 8 | 8 | 8 | 28 |

| A4 | 2 | 5 | 6 | 14 | 27 |

| TOTAL | 22 | 24 | 23 | 40 | 109 |

+-------+------+------+------+------+-------+

5 rows in set (0.00 sec)

mysql>3. 利用SUM(IF()) 生成列,直接生成结果不再利用子查询

mysql>

select

ifnull(c1,'total'),

sum(if(c2='B1',C3,0)) AS B1,

sum(if(c2='B2',C3,0)) AS B2,

sum(if(c2='B3',C3,0)) AS B3,

sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL

from tx

group by C1 with rollup ;

+--------------------+------+------+------+------+-------+

| ifnull(c1,'total') | B1 | B2 | B3 | B4 | TOTAL |

+--------------------+------+------+------+------+-------+

| A1 | 9 | 2 | 1 | 11 | 23 |

| A2 | 7 | 9 | 8 | 7 | 31 |

| A3 | 4 | 8 | 8 | 8 | 28 |

| A4 | 2 | 5 | 6 | 14 | 27 |

| total | 22 | 24 | 23 | 40 | 109 |

+--------------------+------+------+------+------+-------+

5 rows in set (0.00 sec)

mysql>4. 动态，适用于列不确定情况，

mysql> SET @EE='';

mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;

mysql> SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');

Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt2 FROM @QQ;

Query OK, 0 rows affected (0.00 sec)

Statement prepared

mysql> EXECUTE stmt2;

+--------------------+------+------+------+------+-------+

| ifnull(c1,'total') | B1 | B2 | B3 | B4 | TOTAL |

+--------------------+------+------+------+------+-------+

| A1 | 9 | 2 | 1 | 11 | 23 |

| A2 | 7 | 9 | 8 | 7 | 31 |

| A3 | 4 | 8 | 8 | 8 | 28 |

| A4 | 2 | 5 | 6 | 14 | 27 |

| total | 22 | 24 | 23 | 40 | 109 |

+--------------------+------+------+------+------+-------+

5 rows in set (0.00 sec)

mysql>

其实数据库中也可以用 CASE WHEN / DECODE 代替 IF

sum(if(c2='B1',C3,0)) AS B1

可改写为

sum(case c2 when 'B1' then C3 else 0 end) AS B1

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

代码区博客精选文章

分页：12

转载请注明

本文标题：mysql行列转换方法总结 mysql mysql数据库 mysql函数 行列转换方法

本站链接：/view/546649.html

1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责；

2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性，不作出任何保证或承若；

3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。

登录后可拥有收藏文章、关注作者等权限...