首页 > 代码库 > mysql 行转列
mysql 行转列
准备工作：
create table tx( id int primary key, c1 char(2), c2 char(2), c3 int ); insert into tx values(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);
先看看数据格式：
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)
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 preparedmysql> 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>
另外：
如果您是在navicat for maysql 中的查询中测试，请输入:
SET @EE=‘‘;
select @EE :=CONCAT(@EE,‘sum(if(C2= \‘‘,C2,‘\‘,C3,0)) as ‘,C2, ‘,‘) FROM (SELECT DISTINCT C2 FROM tx) A;
SET @QQ=CONCAT(‘SELECT ifnull(c1,\‘total\‘),‘,LEFT(@EE,LENGTH(@EE)-1),‘ ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP‘);
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2
其实数据库中也可以用 CASE WHEN / DECODE 代替 IF
（转自：http://blog.csdn.net/zhoushengchao/article/details/7321688）
mysql 行转列
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。