首页 > 代码库 > mysql 行转列

mysql 行转列

2024-07-22 02:43:02   211人阅读

准备工作：

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 行转列


联系
我们