假设我有一个由像这样的条目组成的表
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排的出发城市,但它们仍然应该合并.实际上,一个主要目标是更好地了解数据中有多少个洞.
解决方法
解决方案是使用CTE并考虑两个连续行(由rowno标识)之间的差异始终为1(并且还考虑日期)：
;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)
)
