SQL Server：如何将出发地与正确的目的地及日期关联？

我明白你现在的困扰——用简单的自连接虽然能筛选出同一卡车的城市组合，但因为没限定行程的时间顺序，导致日期配对完全混乱，出现了逻辑矛盾的结果（比如半年后的到达日期和当前出发日期配对）。问题的核心是：你需要的是连续的行程关联，而不是同一卡车所有记录的两两配对。

核心思路 #

我们需要给每辆卡车的所有行程按时间顺序编号，这样就能精准关联“上一段出发行程”和“下一段到达行程”。这里用SQL Server的ROW_NUMBER()窗口函数就能实现，它可以按卡车分组，再按到达日期排序，给每段行程一个唯一的顺序号。

正确的查询语句 #

WITH RankedMovements AS (
    SELECT 
        UniqueID,
        City,
        -- 注意：如果你的日期是字符串格式的dd/mm/yyyy，必须转换为日期类型
        CONVERT(date, ArrivalDate, 103) AS ArrivalDate,
        CONVERT(date, SentDate, 103) AS SentDate,
        -- 按卡车分组，以到达日期排序生成顺序号
        ROW_NUMBER() OVER (PARTITION BY UniqueID ORDER BY CONVERT(date, ArrivalDate, 103)) AS rn
    FROM Movedata
    -- 先过滤时间范围，减少数据处理量
    WHERE CONVERT(date, SentDate, 103) BETWEEN '2020-01-28' AND '2021-01-29'
)
SELECT 
    a.UniqueID,
    a.City AS 'City 1',
    b.City AS 'City 2',
    a.SentDate AS 'SentDate',
    b.ArrivalDate AS 'ArrivalDate'
FROM RankedMovements a
INNER JOIN RankedMovements b 
    ON a.UniqueID = b.UniqueID 
    AND a.rn = b.rn - 1
-- 如果你需要筛选特定的出发/到达城市，比如从洛杉矶到波士顿，可以添加以下条件
-- WHERE a.City LIKE '%Los Angeles%' AND b.City = 'Boston'
ORDER BY a.UniqueID, a.SentDate DESC;

代码解释 #

1. CTE RankedMovements：

   • 把字符串格式的日期转换为SQL Server能识别的日期类型（用CONVERT(date, 字段, 103)，103对应dd/mm/yyyy格式），避免排序错误。
   • 用ROW_NUMBER()给每辆卡车的行程按到达日期排序编号，同一卡车的最早行程rn=1，下一段行程rn=2，以此类推。

2. 自连接逻辑：

   • 通过a.rn = b.rn - 1关联连续的两段行程：a是出发段（从City1出发，SentDate为出发时间），b是到达段（到达City2，ArrivalDate为到达时间）。
   • 这样就能保证日期逻辑的正确性：卡车从City1出发后，接下来到达的是City2，不会出现跨多个行程的错误配对。

额外说明 #

如果你的日期字段本身就是date或datetime类型，直接去掉CONVERT函数即可，只保留原始字段进行排序和过滤。

内容的提问来源于stack exchange，提问作者Zamec