sql - 通过满足 WHERE 子句的特定 ORDER BY 获取下一个结果

Question

给定一个 TripID，我需要获取满足特定条件的下一个结果（TripSource <> 1 AND HasLot = 1），但我发现考虑“下一次旅行”的顺序必须是“ORDER BY TripDate, TripOrder ”。所以我的意思是 TripID 与订单无关。

（我使用的是 SQL Server 2008，所以我不能使用 LEAD 或 LAG，但我也对使用它们的答案感兴趣。）

示例数据源：

    +--------+-------------------------+-----------+------------+--------+
    | TripID | TripDate                | TripOrder | TripSource | HasLot |
    +--------+-------------------------+-----------+------------+--------+
 1. | 37172  | 2019-08-01 00:00:00.000 |     0     |      1     |    0   |
 2. | 37211  | 2019-08-01 00:00:00.000 |     1     |      1     |    0   |
 3. | 37198  | 2019-08-01 00:00:00.000 |     2     |      2     |    1   |
 4. | 37213  | 2019-08-01 00:00:00.000 |     3     |      1     |    0   |
 5. | 37245  | 2019-08-02 00:00:00.000 |     0     |      1     |    0   |
 6. | 37279  | 2019-08-02 00:00:00.000 |     1     |      1     |    0   |
 7. | 37275  | 2019-08-02 00:00:00.000 |     2     |      1     |    0   |
 8. | 37264  | 2019-08-02 00:00:00.000 |     3     |      2     |    0   |
 9. | 37336  | 2019-08-03 00:00:00.000 |     0     |      1     |    1   |
10. | 37320  | 2019-08-05 00:00:00.000 |     0     |      1     |    0   |
11. | 37354  | 2019-08-05 00:00:00.000 |     1     |      1     |    0   |
12. | 37329  | 2019-08-05 00:00:00.000 |     2     |      1     |    0   |
13. | 37373  | 2019-08-06 00:00:00.000 |     0     |      1     |    0   |
14. | 37419  | 2019-08-06 00:00:00.000 |     1     |      1     |    0   |
15. | 37421  | 2019-08-06 00:00:00.000 |     2     |      1     |    0   |
16. | 37414  | 2019-08-06 00:00:00.000 |     3     |      1     |    1   |
17. | 37459  | 2019-08-07 00:00:00.000 |     0     |      2     |    1   |
18. | 37467  | 2019-08-07 00:00:00.000 |     1     |      1     |    0   |
19. | 37463  | 2019-08-07 00:00:00.000 |     2     |      1     |    0   |
20. | 37461  | 2019-08-07 00:00:00.000 |     3     |      0     |    0   |
    +--------+-------------------------+-----------+------------+--------+

我需要的结果：

Given TripID37211 (Row 2.)我需要获取37198 (Row 3.)
Given TripID37198 (Row 3.)我需要获取37459 (Row 17.)
Given TripID37459 (Row 17.)我需要获取null
Given TripID37463 (Row 19.)我需要获取null

标签： sqlsql-server

Answer 1

您可以使用相关子查询或outer apply：

select t.*, t2.tripid
from trips t outer apply
     (select top (1) t2.*
      from trips t2
      where t2.tripsource <> 1 and t2.haslot = 1 and
            (t2.tripdate > t.tripdate or
             t2.tripdate = t.tripdate and t2.triporder > t.triporder
            )
      order by t2.tripdate desc, t2.triporder desc
     ) t2;