时间戳

首页 > 解决方案 > 如何在自联接中编写随行的当前迭代而变化的条件

mysql - 如何在自联接中编写随行的当前迭代而变化的条件

问题描述

一个表（测试）有一个描述

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| task  | varchar(2)  | NO   |     | NULL    |       |
| time  | int(11)     | NO   |     | NULL    |       |
| type  | char(1)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

并包含数据

+------+------+------+
| task | time | type |
+------+------+------+
| T1   |    1 | S    |
| T2   |    2 | S    |
| T1   |    7 | E    |
| T1   |    8 | S    |
| T1   |   14 | E    |
| T2   |   15 | E    |
| T1   |   16 | S    |
| T2   |   17 | S    |
| T3   |   20 | S    |
| T1   |   21 | E    |
| T3   |   25 | E    |
+------+------+------+

表示在某个时间单位开始（S）或完成（E）的任务的数据集。是否可以以输出包含任务开始时间和结束时间的表的方式加入它。这里 (T2, 17, S) 在最终输出中被跳过，因为还没有关于它的结束时间的数据。

最后结果：-

+------+------+------+
| task | start| end  |
+------+------+------+
| T1   |    1 | 7    |
| T2   |    2 | 15   |
| T1   |    8 | 14   |
| T1   |   16 | 21   |
| T3   |   20 | 25   |
+------+------+------+

从最终结果中可以看出，任务 T (T1) 的所有时间框架都是互斥的 [(1,7),(8,15),(16,25)]。

无法弄清楚加入的条件规则

select S_table.task, S_table.time as start, E_table.time as end
from (select * from test where type='S') as S_table
left join (select * from test where type='E') as E_table
on
S_table.task = E_table.task
and
E_table.time should be greater than previous E_table.time for same task
and
E_table.time should be least within S_table.time < E_table.time

标签： mysqlsql

解决方案

对于每个开始时间，您需要获取大于该开始时间的“E”类型的最小时间：

select t.* from (
  select 
    t.task,
    t.time start,
    (select min(time) from test where type = 'E' and task = t.task and time > t.time) end
  from test t 
  where t.type = 'S' 
) t
where t.end is not null

请参阅演示。
结果：

| task | start | end |
| ---- | ----- | --- |
| T1   | 1     | 7   |
| T2   | 2     | 15  |
| T1   | 8     | 14  |
| T1   | 16    | 21  |
| T3   | 20    | 25  |

您可以像您的代码一样使用内部自联接获得相同的结果：

select S_table.task, S_table.time as start, E_table.time as end
from (select * from test where type='S') as S_table
inner join (select * from test where type='E') as E_table
on
S_table.task = E_table.task
and 
E_table.time = (
  select min(time) from test where type = 'E' and task = S_table.task and time > S_table.time
)
order by S_table.time

推荐阅读