MySQL动态行转列

MySQL动态行转列

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

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

> 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名字的对应表

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)

然后

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

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