开发者

MySQL - Limit a left join to the first date-time that occurs?

开发者 https://www.devze.com 2023-01-02 20:49 出处:网络
Simplified table structure (the tables can\'t be merged at this time): TableA: dts_received (datetime) dts_completed (datetime)

Simplified table structure (the tables can't be merged at this time):

TableA:

dts_received (datetime)
dts_completed (datetime)
task_a (varchar)

TableB:

dts_started (datetime)
task_b (varchar)

What I would like to do is determine how long a task took to 开发者_运维百科complete.

The join parameter would be something like

ON task_a = task_b AND dts_completed < dts_started

The issue is that there may be multiple date-times that occur after the dts_completed.

How do I create a join that only returns the first tableB-datetime that occurs after the tableA-datetime?


select a.task_a as task, min(b.dts_started) as dts_started
from TableA a
inner join TableB b on a.task_a = b.task_b 
    and a.dts_completed < b.dts_started 
GROUP BY a.task_a


SELECT task_a, dts_completed,
bb.started
FROM tableA
INNER JOIN 
(SELECT task_b, MIN(dts_started) as started
FROM tableB GROUP BY taks_b)bb
ON (bb.task_b = tableA.task_a AND bb.started > tableA.dts_completed)
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号