通过Rank链接的SQL（）超过

Question

我必须计算容器退出设施的日子，每次容器到达时它都有不同的主键但ID相同。相同的集装箱可以多次出发和抵达，但我当然必须计算最近出发和到达之间的日期。我一直在尝试这样做，使用RANK（）函数以上通过计算出发地和目的地：

SELECT DISTINCT *, datediff(day,c_out.time_out, c_in.time_In) as Days 
FROM 

(SELECT Container_ID 
, time_out 
, RANK() OVER (PARTITION by Container_ID, order by time_out) as leave_no 
FROM Containers 
WHERE Departure_type='TRUCK'    --EDIT2 ************************************ 
) c_out INNER JOIN 
(SELECT container_ID as Incoming_ID 
, time_in 
,RANK() OVER (PARTITION by Container_ID, order by time_in) as arrive_no 
FROM Containers) c_in 
ON c_out.container_id=c_in.incoming_id 

WHERE c_out.leave_no=c_in.arrive_no+1 

这里的想法是树叶匹配项：如果容器左侧为n个时，接下来的到来将是n + 1

但作为一个结果，我收到类似

CONTAINER_ID TIME_OUT LEAVE_NO INCOMING_ID TIME_IN ARRIVE_NO DAYS 
ABC123   2014-04-11  2  ABC123  2013-11-21 1   -141 

编辑：尽管下面给出的解决方案做的工作，我还是想知道如何加入的RANK（）

EDIT2：让我澄清一下：容器具有特别的方式（一些可能一个）

Answer 1

你可以尝试更多的东西蛮力，要离开有点像：

SELECT Container_ID 
, time_out 
, (select min(c2.time_in) 
     from Containers c2 
     where c2.container_ID = c1.container_ID 
     and c2.time_in > c1.time_out) as returned 
FROM Containers c1 

Answer 2

尝试所有可能做的排名计算一次：

with cte as (
    SELECT Container_ID 
     , time_out 
     , time_in 
     , RANK() OVER (PARTITION by Container_ID order by time_out) as leave_no 
     , RANK() OVER (PARTITION by Container_ID order by time_in) as arrive_no 
    FROM Containers 
) 
select c_out.container_no 
    , c_out.time_out 
    , c_in.time_in, datediff(day,c_out.time_out, c_in.time_In) 
from cte as c_out 
join cte as c_in 
    on c_out.container_id = c_in.container_id 
    and c_out.leave_no = c_in.arrive_no + 1