sql - 确定到达和离开的成对以计算在目的地花费的时间

Question

我正在尝试编写一个 SQL 查询，让我知道根据到达和离开时间在特定机场花费的时间。

CREATE TABLE Flights
    (`ACID` varchar(6), `TIME` datetime, `STATUS` varchar(1))
;

INSERT INTO Flights
    (`ACID`, `TIME`, `STATUS`)
VALUES
    ('XXXX', '2014-01-01 18:09:00', 'A'),
    ('XXXX', '2014-01-02 15:25:00', 'D'),
    ('XXXX', '2014-01-06 19:53:00', 'A'),
    ('XXXX', '2014-01-07 14:01:00', 'D'),
    ('XXXX', '2014-01-12 16:06:00', 'A'),
    ('XXXX', '2014-01-12 17:00:00', 'D'),
    ('YYYY', '2014-01-13 18:10:00', 'A'),
    ('YYYY', '2014-01-13 18:56:00', 'D'),
    ('XXXX', '2014-01-16 20:40:00', 'A'),
    ('XXXX', '2014-01-17 14:49:00', 'D'),
    ('ZZZZ', '2014-01-17 21:42:00', 'A'),
    ('ZZZZ', '2014-01-17 22:17:00', 'D'),
    ('XXXX', '2014-01-21 00:30:00', 'A'),
    ('XXXX', '2014-01-23 15:35:00', 'D')
;

状态行是“A”表示到达或“D”表示离开。

输出应该汇总 ACID 字段花费的时间(即出发时间和到达时间之间的差值)。

可能会有到达记录，但没有匹配的出发记录的情况。

我很难“确定配对”，以便我可以计算日期差异等。

关系数据库管理系统是 MSSQL 2012

Answer 1

试试这个解决方案 http://sqlfiddle.com/#!6/48c90/17 :

SELECT ACID, DATEDIFF(HH, TIME ,
(SELECT TOP 1 b.TIME FROM Flights b WHERE b.STATUS = 'D' AND b.TIME > a.TIME))
FROM  Flights a
WHERE STATUS = 'A'

它给你这个结果:

| ACID | COLUMN_1 |
|------|----------|
| XXXX |       21 |
| XXXX |       19 |
| XXXX |        1 |
| YYYY |        0 |
| XXXX |       18 |
| ZZZZ |        1 |
| XXXX |       63 |

您可以将 DATEDIFF(HH 更改为 DATEDIFF(DD 以天为单位进行差异。

看看here