选择动态列Mysql

Question

我很难弄清楚该怎么问这个问题。我有两个表，一个是带有注册表时间的“Orders”列表，另一个是表“Activity”，其中我有这些订单发送到进程的时间的寄存器。

我不知道该怎么做的是下一件事，我需要能够用一个查询来确定自上一个过程以来每个订单的时间。

表: Ordenes

| Id | Numero | flujo_id | LastOrder |            FechaRegistro |
|----|--------|----------|-----------|--------------------------|
|  1 |    111 |        1 |         0 | August, 13 2015 13:25:00 |
|  2 |    222 |        1 |         2 | August, 13 2015 13:25:00 |
|  3 |    333 |        1 |         4 | August, 13 2015 13:25:00 |
|  4 |    444 |        1 |         3 | August, 13 2015 13:25:00 |

表:活动

| Id |             TiempoInicio | TiempoFin | Proceso_Id | Orden_Id |
|----|--------------------------|-----------|------------|----------|
|  7 | August, 13 2015 13:30:00 |    (null) |          1 |        2 |
|  8 | August, 13 2015 13:50:00 |    (null) |          2 |        2 |
|  9 | August, 13 2015 13:30:00 |    (null) |          1 |        3 |
| 10 | August, 13 2015 13:50:00 |    (null) |          2 |        3 |
| 11 | August, 13 2015 14:20:00 |    (null) |          3 |        3 |
| 12 | August, 13 2015 14:25:00 |    (null) |          4 |        3 |
| 13 | August, 13 2015 13:30:00 |    (null) |          1 |        4 |
| 14 | August, 13 2015 13:50:00 |    (null) |          2 |        4 |
| 15 | August, 13 2015 14:20:00 |    (null) |          3 |        4 |

表: Procesos

| Id | Estacion_Id |                                             Nombre |                 TiempoStd |
|----|-------------|----------------------------------------------------|---------------------------|
|  1 |           1 |      Informacion <br/>tecnica para <br/>aprobacion | January, 01 1970 00:02:00 |
|  2 |           2 | Aprobacion de<br/> Informacion<br/>Tecnica cliente | January, 01 1970 00:24:00 |
|  3 |           3 |             Informacion<br/>Tecnica de<br/>Proceso | January, 01 1970 00:04:00 |
|  4 |           4 |           Compra y<br/>Recepcion de<br/>Materiales | January, 01 1970 02:00:00 |

预期产出。

| Id | Numero |            FechaRegistro |   P1   |   P2   |   P3   |   P4   |
|----|--------|--------------------------|--------|--------|--------|--------|
|  1 |    111 | August, 13 2015 13:25:00 | (null) | (null) | (null) | (null) |
|  2 |    222 | August, 13 2015 13:25:00 | 5:00   | 20:00  | (null) | (null) |
|  3 |    333 | August, 13 2015 13:25:00 | 5:00   | 20:00  | 30:00  | 5:00   |
|  4 |    444 | August, 13 2015 13:25:00 | 5:00   | 20:00  | 30:00  | (null) |

我不知道如何进行动态调用，以便得到P1、P2、P3，直到Pn列n= 'Procesos‘表上的行数。对于时间计算，我确实有一个想法，但由于我不知道如何得到这些列，所以我无法尝试。

这是摆满桌子的小提琴。这里

Answer 1

我想出了一个类似于下面的东西。可能看上去有点模糊。

select ordenes.Id,Numero,FechaRegistro,m.p1,a.p2,b.p3,c.p4 from Ordenes 
left join (select ordenes.id,
              TIMESTAMPDIFF(MINUTE,Ordenes.FechaRegistro,min(Actividad.TiempoInicio )) P1 
           from Ordenes
           left join Actividad on ordenes.id=Actividad.orden_id
           group by Actividad.orden_id) as m on ordenes.id=m.id
left join (select Orden_Id,TIMESTAMPDIFF(MINUTE,min(TiempoInicio ),max(TiempoInicio )) P2 
           from actividad 
           where Proceso_Id in ('1','2')
           group by orden_id) as a  on ordenes.id=a.orden_id
left join 
(select Orden_Id,TIMESTAMPDIFF(MINUTE,min(TiempoInicio ),max(TiempoInicio )) P3 
        from actividad 
        where Proceso_Id in ('2','3')
        group by orden_id) as b on ordenes.id=b.orden_id
left join 
(select Orden_Id,TIMESTAMPDIFF(MINUTE,min(TiempoInicio ),max(TiempoInicio )) P4 
        from actividad 
        where Proceso_Id in ('3','4')
        group by orden_id) as c on ordenes.id=c.orden_id
order by id;

我得到的输出

| Id | Numero |            FechaRegistro |   P1   |   P2   |   P3   |   P4   |
|----|--------|--------------------------|--------|--------|--------|--------|
|  1 |    111 | August, 13 2015 13:25:00 | (null) | (null) | (null) | (null) |
|  2 |    222 | August, 13 2015 13:25:00 | 5      | 20     | 0      | (null) |
|  3 |    333 | August, 13 2015 13:25:00 | 5      | 20     | 30     | 5      |
|  4 |    444 | August, 13 2015 13:25:00 | 5      | 20     | 30     | 0      |

It 并不能满足的所有要求。但至少可以作为进一步研究的参考。

演示Fiddle