按特定顺序选择动态行值作为列名 [英] Select dynamic row values as column names, in specific order

问题描述

继续我的上一个问题:我必须将动态行值用作列，但是列的顺序需要具体.如果您看下面的示例，则必须按start_time的升序对选定的列进行排序.

In continuation with my previous question: I have to use dynamic row values as columns but the order of the columns needs to be specific. If you look at the following example, 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            |
+---------+------------+-------------------+----------+---------------+---------------+

上一个问题:

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;

以上查询将返回以下结果:

Above query will return following result:

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函数中添加ORDER BY start_time ASC来做到这一点

You can do this by adding ORDER BY start_time ASC in your GROUP_CONCAT function

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`' 
         )
          ORDER BY start_time ASC        
 )
  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;

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