SQL Server - 获取大于特定值的最接近值

debugcn 发表于 Dev

杰克

我有一张表格，其中包含每个订单的到达时间。

订单到货时间

order_id   arrival_time
    1               120
    2               239
    3               500           
    ...

对于每个特定订单，它有多个卡车出发时间可供选择，可以从下表中查询：

Truck_departure_time

Order_id     Truck_id           deppature_time
 1            truck_0             66
 1            truck_1             125
 1            truck_2             300
 1            truck_3             720
 1            truck_4             900
 1            ...

我想在到达时间后将每个订单与最近的卡车出发时间相匹配，例如：

order_id   arrival_time    depature_time
    1               120          125
    2               239          300
    3               500          720   
    ...

伊莱

您可以使用窗口函数作为

CREATE TABLE T1(
  OrderID INT,
  ArrivalTime INT
);

CREATE TABLE T2(
  OrderID INT,
  TruckID VARCHAR(45),
  DepartureTime INT
);

INSERT INTO T1 VALUES
(1 ,120),
(2 ,239),
(3 ,500);

INSERT INTO T2 VALUES
(1, 'truck_0' , 66),
(1, 'truck_1' , 125),
(1, 'truck_2' , 300),
(2, 'truck_3' , 720),
(2, 'truck_4' , 900);

WITH C AS
(
SELECT T1.OrderID,
       T1.ArrivalTime,
       T2.DepartureTime,
       ROW_NUMBER() OVER(PARTITION BY T2.OrderID ORDER BY T2.DepartureTime) RN
FROM T1 INNER JOIN T2
     ON T1.OrderID = T2.OrderID
)
SELECT OrderID,
       ArrivalTime,
       DepartureTime
FROM C
WHERE RN = 2;

返回：

+---------+-------------+---------------+
| OrderID | ArrivalTime | DepartureTime |
+---------+-------------+---------------+
|       1 |         120 |           125 |
|       2 |         239 |           900 |
+---------+-------------+---------------+

演示

更新：

SELECT T1.OrderID,
       T1.Arrivaltime,
       TT.DepartureTime
FROM
(
  SELECT T1.OrderID,
         T1.ArrivalTime,
         T2.DepartureTime,
         ROW_NUMBER() OVER(PARTITION BY T2.OrderID ORDER BY T1.OrderID) RN
  FROM T1 CROSS JOIN T2
) TT INNER JOIN T1
ON T1.OrderID = TT.RN - 1;

返回：

+---------+-------------+---------------+
| OrderID | Arrivaltime | DepartureTime |
+---------+-------------+---------------+
|       1 |         120 |           125 |
|       2 |         239 |           300 |
|       3 |         500 |           720 |
+---------+-------------+---------------+

演示

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。