MySQL动态行转列

通过max行转列不是动态的, 你还是要知道所有列名才可以

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@localhost 17:46:56 [fanboshi]> select * from t3;
+----+---------+------+-----------+
| id | title | env | progress |
+----+---------+------+-----------+
| 1 | 工单1 | 1 | 完成 |
| 2 | 工单1 | 2 | 完成 |
| 3 | 工单1 | 3 | 待审核 |
| 4 | 工单2 | 1 | 待审核 |
+----+---------+------+-----------+
4 rows in set (0.00 sec)

root@localhost 17:48:20 [fanboshi]> select title,max(if(env=1,progress,-1)) 'RC',max(if(env=2,progress,-1)) 'Stage',max(if(env=3,progress,-1)) 'Prod' from t3 group by title;
+---------+-----------+--------+-----------+
| title | RC | Stage | Prod |
+---------+-----------+--------+-----------+
| 工单1 | 完成 | 完成 | 待审核 |
| 工单2 | 待审核 | -1 | -1 |
+---------+-----------+--------+-----------+
2 rows in set (0.00 sec)

oracle有pivot函数可以解决这个问题,MySQL并没有这样的函数, 一种退而求其次的办法是使用JSON_OBJECTAGG

1
2
3
4
5
6
7
> select title,JSON_OBJECTAGG(env,progress) val from t3 group by title;
+---------+--------------------------------------------------+
| title | val |
+---------+--------------------------------------------------+
| 工单1 | {"1": "完成", "2": "完成", "3": "待审核"} |
| 工单2 | {"1": "待审核"} |
+---------+--------------------------------------------------+

然后再程序循环val中的key,value自己做处理

使用prepare statement可以实现, 本例需要构造一个env值与env名字的对应表

1
2
3
4
5
6
7
8
9
root@localhost 17:42:07 [fanboshi]> select * from t_env;
+----+--------+----------+
| id | env_id | env_name |
+----+--------+----------+
| 1 | 1 | RC |
| 2 | 2 | Stage |
| 3 | 3 | Prod |
+----+--------+----------+
3 rows in set (0.00 sec)

然后

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(pa.env = ''',
env,
''', pa.progress, -1)) AS ',
"'",e.env_name,"'"
)
) INTO @sql
FROM t3,(select @sql:= NULL) tmp, t_env e where env = e.env_id;

root@localhost 17:44:40 [fanboshi]> select @sql;
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| MAX(IF(pa.env = '1', pa.progress, NULL)) AS 'RC',MAX(IF(pa.env = '2', pa.progress, NULL)) AS 'Stage',MAX(IF(pa.env = '3', pa.progress, NULL)) AS 'Prod' |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+


select @sql:=CONCAT('SELECT pa.title
, ', @sql, '
FROM t3 pa
GROUP BY pa.title');

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql:=CONCAT('SELECT pa.title
, ', @sql, '
FROM t3 pa
GROUP BY pa.title') |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT pa.title
, MAX(IF(pa.env = '1', pa.progress, -1)) AS 'RC',MAX(IF(pa.env = '2', pa.progress, -1)) AS 'Stage',MAX(IF(pa.env = '3', pa.progress, -1)) AS 'Prod'
FROM t3 pa
GROUP BY pa.title |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
其实这里已经把拼接好的sql查出来了, 程序直接拿着个结果去执行应该也是可以的, 可能不用后面在使用prepare statement

PREPARE stmt FROM @sql;
EXECUTE stmt;
+---------+-----------+--------+-----------+
| title | RC | Stage | Prod |
+---------+-----------+--------+-----------+
| 工单1 | 完成 | 完成 | 待审核 |
| 工单2 | 待审核 | -1 | -1 |
+---------+-----------+--------+-----------+
DEALLOCATE PREPARE stmt;

那么基于上面的思路, 我寻思还用得着prepare statement吗, 直接拼个sql不就完事了吗, 用SQL造SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT 
CONCAT('SELECT pa.title, ',
GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(pa.env = \'',
env,
'\', pa.progress, -1)) AS ',
'\'',
e.env_name,
'\'')),
' FROM t3 pa group by pa.title') final_sql
FROM
t3,
t_env e
WHERE
env = e.env_id;

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| final_sql |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT pa.title, MAX(IF(pa.env = '1', pa.progress, -1)) AS 'RC',MAX(IF(pa.env = '2', pa.progress, -1)) AS 'Stage',MAX(IF(pa.env = '3', pa.progress, -1)) AS 'Prod' FROM t3 pa group by pa.title |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

> SELECT pa.title, MAX(IF(pa.env = '1', pa.progress, -1)) AS 'RC',MAX(IF(pa.env = '2', pa.progress, -1)) AS 'Stage',MAX(IF(pa.env = '3', pa.progress, -1)) AS 'Prod' FROM t3 pa group by pa.title;
+---------+-----------+--------+-----------+
| title | RC | Stage | Prod |
+---------+-----------+--------+-----------+
| 工单1 | 完成 | 完成 | 待审核 |
| 工单2 | 待审核 | -1 | -1 |
+---------+-----------+--------+-----------+
2 rows in set (0.00 sec)


参考:

https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2022 Fan() All Rights Reserved.

访客数 : | 访问量 :