在mysql中将一行按最小日期转换为两行

Question

我的桌子还有下一个问题。我需要使用一个sql查询将下一个表转换为另一个表：

id    | idal   | date        | time         | idaction 
----- | ------ | ----------- | ------------ | ----------
1001  |      1 |   2016-08-1 |     13:30:39 |        1
1002  |      1 |   2016-08-1 |     13:42:02 |        2
1003  |      1 |   2016-08-1 |     13:42:04 |        1
1004  |      1 |   2016-08-1 |     13:42:06 |        8
1005  |      1 |   2016-08-1 |     13:44:04 |        9
1006  |      1 |   2016-08-1 |     13:44:06 |        8
1007  |      2 |   2016-08-1 |     14:30:39 |        1
1008  |      2 |   2016-08-1 |     14:42:02 |        2
1009  |      2 |   2016-08-1 |     14:42:06 |        8
1010  |      3 |   2016-08-1 |     15:00:01 |        1
1011  |      3 |   2016-08-1 |     15:01:43 |        2

我想得到的下一个结果是：

idal | dt_act1             | dt_act8            |
-------------------------------------------------
   1 |  2016-08-1 13:30:39 |  2016-08-1 13:42:06    
   2 |  2016-08-1 14:30:39 |  2016-08-1 14:42:06

这个复杂查询的意义是下一个:我希望为每组相同的idal值(我们有1、2和3)，只有那些至少包含idaction=1和8的值(在我们的示例中，只有id1和2包含这两个值)。同时，结果字段dt_act1和dt_act8是一个日期时间字段，在主表中它被划分为日期和时间字段(您可以使用concat函数将其连接到一个datatime字段中，这没有问题)。您可以注意到，dt_act1是idaction=1的每个组的最小日期时间，对于字段dt_act8和idaction=8是相同的。当没有包含idaction 8和1(在我们的例子中是idal=3)的idaction 8和1的组时，将不会显示这一行。

我的解决方案行不通：

SELECT t1.idal, t1.dt AS dt_act1, t2.dt AS dt_act8
FROM 
      (SELECT tt1.idal, CONCAT(tt1.`date`, ' ', tt1.`time`) AS dt FROM `table` tt1 (*)
       HAVING dt = (SELECT MIN(CONCAT(tt2.`date`, ' ' , tt2.`time`)) 
                    FROM `table` tt2 WHERE tt1.idal=tt2.idal AND tt1.idaction=1
                    )
      ) t1 
   INNER JOIN 
      (SELECT tt3.idal, CONCAT(tt3.`date`, ' ', tt3.`time`) AS dt FROM `table` tt3 (**)
       HAVING dt = (SELECT MIN(CONCAT(tt4.`date`, ' ' , tt4.`time`)) 
                    FROM `table` tt4 WHERE tt3.idal=tt4.idal AND tt3.idaction=8
                    )
      ) t2
   ON t1.idal=t2.idal 

这是应该的，但不起作用。怎么做呢？

解决方案:在(*)和(**)后面分别保留"WHERE tt1.idaction=1“和"WHERE tt3.idaction=8”。

Answer 1

我将在if()表达式中使用一个条件语句(case或min() )来获得相关idactions的最小值。然后使用having子句消除没有两个idactions的数据的记录。

模式：

create table yourtable
(   `id` int not null,
    `idal` int not null,
    `date` date not null, -- don't name columns like this
    `time` time not null,  -- don't name columns like this
    `idaction` int not null
);

insert yourtable values
(1001,1,'2016-08-1','13:30:39',1),
(1002,1,'2016-08-1','13:42:02',2),
(1003,1,'2016-08-1','13:42:04',1),
(1004,1,'2016-08-1','13:42:06',8),
(1005,1,'2016-08-1','13:44:04',9),
(1006,1,'2016-08-1','13:44:06',8),
(1007,2,'2016-08-1','14:30:39',1),
(1008,2,'2016-08-1','14:42:02',2),
(1009,2,'2016-08-1','14:42:06',8),
(1010,3,'2016-08-1','15:00:01',1),
(1011,3,'2016-08-1','15:01:43',2);

查询：

select idal, 
       min(if(idaction=1,concat(`date`, ' ', `time`),null)) as dt_act1, 
       min(if(idaction=8,concat(`date`, ' ', `time`),null)) as dt_act8 
from yourtable 
group by idal 
having dt_act1 is not null and dt_act8 is not null; 

结果：

+------+---------------------+---------------------+
| idal | dt_act1             | dt_act8             |
+------+---------------------+---------------------+
|    1 | 2016-08-01 13:30:39 | 2016-08-01 13:42:06 |
|    2 | 2016-08-01 14:30:39 | 2016-08-01 14:42:06 |
+------+---------------------+---------------------+