mysql 用行实现动态列,MySql选择动态行值作为列名

这是我给出的表格：

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

| exec_id | project_id | flow_id | job_id | start_time | end_time |

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

| 10919 | 16 | my_flow_cleanup | init | 1408480308611 | 1408480308686 |

| 10919 | 16 | my_flow_cleanup | job_id_1 | 1408480309212 | 1408480309426 |

| 10919 | 16 | my_flow_cleanup | job_id_2 | 1408480308721 | 1408480308776 |

| 10919 | 16 | my_flow_cleanup | job_id_3 | 1408480308827 | 1408480309171 |

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

我想要实现这样的选择：

exec_id init_start init_end job_id_1_start job_id_1_end job_id_2_start job_id_2_end job_id_3_start job_id_3_end

10919 1408480308611 1408480308686 1408480309212 1408480309426 1408480308721 1408480308776 1408480308827 1408480309171

我花了两天多的时间,但收效甚微. This question对我有所帮助,但并非如此.如您所见,另一个问题涉及获取2列并将其转换为2行.但我必须采取3列并将它们转换为2行.

谁能帮我这个？它甚至可以在MySQL中使用吗？

编辑1

谢谢Khalid Junaid的答案,它解决了我的问题.我还要做一个修改.我必须通过start_time按升序对所选列进行排序.

例如.：

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

| exec_id | project_id | flow_id | job_id | start_time | end_time |

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

| 10919 | 16 | my_flow_cleanup | init | 10 | 15 |

| 10919 | 16 | my_flow_cleanup | job_id_1 | 30 | 40 |

| 10919 | 16 | my_flow_cleanup | job_id_2 | 40 | 50 |

| 10919 | 16 | my_flow_cleanup | job_id_3 | 20 | 25 |

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

当前查询将返回：

exec_id init_start init_end job_id_1_start job_id_1_end job_id_2_start job_id_2_end job_id_3_start job_id_3_end

10919 10 15 30 40 40 50 20 25

我需要：

exec_id init_start init_end job_id_3_start job_id_3_end job_id_1_start job_id_1_end job_id_2_start job_id_2_end

10919 10 15 20 25 30 40 40 50

请注意,根据start_time,列的顺序现在已更改.

我尝试用临时表和视图来做这个,我觉得这很容易.不幸的是,我没有创建表/创建视图权限.有没有办法在没有临时表和视图的情况下实现这一目标？

解决方法:

从参考问题的使用group_concat的方法来看,你可以这样做,但是注意一件事,因为每个exec_id组你的工作id增加,然后group_concat方法不会是最佳的,因为它的默认长度为1024个字符要连接,对于动态列,这肯定会越过这个限制,但这个限制可以增加,如documentation所述

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT

CONCAT('MAX(CASE WHEN job_id = ''',

job_id,

''' THEN start_time END) `',

job_id,

'_start`',

',',

'MAX(CASE WHEN job_id = ''',

job_id,

''' THEN end_time END) `',

job_id,

'_end`'

)

)

INTO @sql

FROM t;

SET @sql = CONCAT('SELECT exec_id, ', @sql, '

FROM t

GROUP BY exec_id');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

标签：sql,mysql,pivot-table

来源： https://codeday.me/bug/20191003/1847833.html