SQL自连接成对

假设我有一个由像这样的条目组成的表

ID    Arrival Date    Arrival City    Departure Date    Departure City
1     Jun 27 2015     Berlin          Jun 20 2015       Paris
1     Jul 1 2015      Rome            Jun 29 2015       Berlin
1     Jul 30 2015     Vienna          Jul 15 2015       Rome
2     Jun 28 2015     Prague          Jun 23 2015       Vienna
2     Jul 1 2015      Rome            Jun 29 2015       Prague
2     Jul 30 2015     Vienna          Jul 15 2015       Moscow
...

并且对于每个ID,我想将这些数据连接到自身,使得随后的出发日期和到达日期的观察成对分组 – 即,出发与每个ID的先前到达配对.

在上面的例子中(为了方便起见,观察结果被排序),第2行将被附加到第1行,第3行到第2行,第5行到第4行以及第6行到第5行(因此产生4行,其中字段ID到达日期抵达城市出发日期出发城市到达日期2抵达城市2出发日期2离境城市2).

每个ID可能有三个以上的离开,因此需要一般方法.另请注意,到达城市和出发城市不匹配的数据中可能存在漏洞 – 例如第5排的到达城市不是第6排的出发城市,但它们仍然应该合并.实际上,一个主要目标是更好地了解数据中有多少个洞.

解决方法

;WITH CTE AS (
SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY t.ID,t.arrivalDate),t.ID,t.arrivalDate,t.arrivalCity,t.departureDate,t.departureCity
FROM #test t
)
SELECT *
FROM CTE c1
JOIN CTE c2
ON c1.ID = c2.ID 
    AND c2.departureDate > c1.arrivalDate
    AND c2.rownum - c1.rownum = 1
GO

-- structure of the #test table
CREATE TABLE #test (
    ID int,arrivalDate date,arrivalCity varchar(30),departureDate date,departureCity varchar(30)
)

SQL小提琴：SQLFiddle

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!