MySQL选择动态行值作为列名 [英] MySql select dynamic row values as column names

问题描述

这是我给定的表格:

+---------+------------+-------------------+--------------------------+---------------+---------------+
| 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

我花了2天以上的时间，但收效甚微. 这个问题帮助了我，但不完全是.如您所见，另一个问题是处理2列并将其转换为2行.但是我必须占用3列并将其转换为2行.

I have spent more than 2 days on this, with very little success. This question helped me, but not completely. As you can see, the other question deals with taking 2 columns and converting them to 2 rows. But I have to take 3 columns and convert them into 2 rows.

有人可以帮我吗?在MySQL中甚至可能吗?

Can anyone help me with this? Is it even possible in MySQL?

感谢Khalid Junaid的回答，它解决了我的问题.我必须再做一个修改.我必须按start_time升序对选定的列进行排序.

Thanks Khalid Junaid for the answer, it solved my problem. I have to make one more modification. I have to order the selected columns by start_time in the ascending order.

例如:

+---------+------------+-------------------+----------+---------------+---------------+
| 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

请注意，根据开始时间，列的顺序现已更改.

Please notice that the order of columns is now changed, according to start_time.

我试图用temporary tables和views做到这一点，我认为这将非常容易.不幸的是，我没有创建表/创建视图的权限.没有临时表和视图，有没有办法做到这一点?

I tried to do this with temporary tables and views, I thought it would be very easy. Unfortunately, I don't have create table/create view permissions. Is there a way to achieve this without temp table and views?

推荐答案

从参考问题的使用group_concat的方法开始，您可以这样做，但是请注意，随着工作ID的增加，每个exec_id组的数量都增加，然后group_concat这种方法不是最佳方法，因为它的默认长度为1024个字符，这对于连接的动态列来说肯定会超过该限制，但是可以如

As from reference question's approach of using group_concat you can do so,but note one thing as your job ids increases per exec_id group then group_concat approach will not be optimal due to its default length of 1024 characters to concatenate and for your dynamic columns this will surely cross that limit,but this limit can be increased as mentioned in 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;

这篇关于MySQL选择动态行值作为列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！