如何从多行IPhone表数据中关联寄送与抵达时间？

某人给在沙特观看世界杯的妻子寄送iPhone 14 Pro Max，运输路线为美国→中国→阿根廷→沙特阿拉伯，对应的运输数据如下：

CREATE TABLE IPhone (Id int, Country NVARCHAR(12), seqNo int, Send datetime2(0), Arrive datetime2(0));

INSERT INTO IPhone VALUES 
('1001','America','1', '2022-11-23 18:30:00.000',null),
('1002','China','2', '2022-11-24 08:18:00.000','2022-11-24 05:00:00'),
('1003','Argentina','3', '2022-11-25 18:30:00.000','2022-11-24 18:18:00.000'),
('1004','Saudi Arabia','4',null,'2022-11-25 20:30:00.000');

期望得到的正确寄达对应关系：

1. 于2022-11-23 18:30:00.000从美国寄出，2022-11-24 05:00:00抵达中国；
2. 于2022-11-24 08:18:00.000从中国寄出，2022-11-24 18:18:00.000抵达阿根廷；
3. 于2022-11-25 18:30:00.000从阿根廷寄出，2022-11-25 20:30:00.000抵达沙特阿拉伯。

解决方案 #

利用表中seqNo字段的运输顺序特性，通过自连接将当前运输段与下一段关联，匹配出发地的寄出时间和目的地的抵达时间：

结构化结果查询 #

SELECT
    i1.Country AS 出发地,
    i1.Send AS 寄出时间,
    i2.Country AS 目的地,
    i2.Arrive AS 抵达时间
FROM
    IPhone i1
INNER JOIN
    IPhone i2 ON i1.seqNo = i2.seqNo - 1
WHERE
    i1.Send IS NOT NULL 
    AND i2.Arrive IS NOT NULL;

执行后输出的结构化结果：

匹配期望文本格式的查询 #

如果需要直接输出和期望格式完全一致的文本，可调整SQL语句：

SELECT
    CONCAT('于 ', i1.Send, ' 从 ', i1.Country, ' 寄出，', i2.Arrive, ' 抵达 ', i2.Country) AS 运输记录
FROM
    IPhone i1
INNER JOIN
    IPhone i2 ON i1.seqNo = i2.seqNo - 1
WHERE
    i1.Send IS NOT NULL 
    AND i2.Arrive IS NOT NULL;

执行后输出的文本结果：

于 2022-11-23 18:30:00 从 America 寄出，2022-11-24 05:00:00 抵达 China
于 2022-11-24 08:18:00 从 China 寄出，2022-11-24 18:18:00 抵达 Argentina
于 2022-11-25 18:30:00 从 Argentina 寄出，2022-11-25 20:30:00 抵达 Saudi Arabia

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