相對于表 1 中的請求日期，如何從表 2 中選擇最接近（之前或之后）收到的日期？最后包括所需的結果。我正在使用 Oracle SQL。
Table1:
PO RequestedDate
14888 01/12/2018
14733 02/12/2018
14555 05/12/2018
Table2:
PO ReceivedDate
14888 01/11/2018
14888 03/12/2018
14733 12/12/2018
14555 07/23/2018
14555 09/23/2018
Expected Result:
PO RequestedDate NearestReceivedDate
14888 01/12/2018 01/11/2018
14733 02/12/2018 12/12/2018
14555 05/12/2018 07/23/2018
uj5u.com熱心網友回復：
一種選擇是對日期差異的絕對值進行排名（當您減去兩個日期資料型別值時，結果是天數），然后獲取排名最高的那些。
樣本資料：
SQL> with
2 tab1 (po, requesteddate) as
3 (select 14888, date '2018-01-12' from dual union all
4 select 14733, date '2018-02-12' from dual union all
5 select 14555, date '2018-05-12' from dual
6 ),
7 tab2 (po, receiveddate) as
8 (select 14888, date '2018-01-11' from dual union all
9 select 14888, date '2018-03-12' from dual union all
10 select 14733, date '2018-12-12' from dual union all
11 select 14555, date '2018-07-23' from dual union all
12 select 14555, date '2018-09-23' from dual
13 ),
查詢從這里開始：
14 temp as
15 (select a.po, a.requesteddate, b.receiveddate,
16 rank() over (partition by a.po order by abs(a.requesteddate - b.receiveddate)) rnk
17 from tab1 a join tab2 b on a.po = b.po
18 )
19 select t.po, t.requesteddate, t.receiveddate as nearestreceiveddate
20 from temp t
21 where t.rnk = 1;
PO REQUESTEDD NEARESTREC
---------- ---------- ----------
14555 05/12/2018 07/23/2018
14733 02/12/2018 12/12/2018
14888 01/12/2018 01/11/2018
SQL>
uj5u.com熱心網友回復：
從 Oracle 12 開始，您可以使用相關子查詢并僅獲取最近的行：
SELECT t1.*,
(
SELECT receiveddate
FROM table2 t2
WHERE t1.po = t2.po
ORDER BY ABS(t1.requesteddate - t2.receiveddate)
FETCH FIRST ROW ONLY
) AS receiveddate
FROM table1 t1;
其中，對于樣本資料：
CREATE TABLE table1 (po, requesteddate) AS
SELECT 14888, DATE '2018-01-12' FROM DUAL UNION ALL
SELECT 14733, DATE '2018-02-12' FROM DUAL UNION ALL
SELECT 14555, DATE '2018-05-12' FROM DUAL;
CREATE TABLE table2 (po, receiveddate) AS
SELECT 14888, DATE '2018-01-11' FROM DUAL UNION ALL
SELECT 14888, DATE '2018-03-12' FROM DUAL UNION ALL
SELECT 14733, DATE '2018-12-12' FROM DUAL UNION ALL
SELECT 14555, DATE '2018-07-23' FROM DUAL UNION ALL
SELECT 14555, DATE '2018-09-23' FROM DUAL;
輸出：
采購訂單 申請日期 收到的日期 14888 2018-01-12 00:00:00 2018-01-11 00:00:00 14733 2018-02-12 00:00:00 2018-12-12 00:00:00 14555 2018-05-12 00:00:00 2018-07-23 00:00:00
db<>在這里擺弄
uj5u.com熱心網友回復：
WITH table1 (po, requesteddate)
AS
(
SELECT 14888, TO_DATE('01/12/2018','MM/DD/YYYY') FROM DUAL UNION ALL
SELECT 14733, TO_DATE('02/12/2018','MM/DD/YYYY') FROM DUAL UNION ALL
SELECT 14555, TO_DATE('05/12/2018','MM/DD/YYYY') FROM DUAL
),
table2 (po, receiveddate)
AS
(
SELECT 14888, TO_DATE('01/11/2018','MM/DD/YYYY') FROM DUAL UNION ALL
SELECT 14888, TO_DATE('03/12/2018','MM/DD/YYYY') FROM DUAL UNION ALL
SELECT 14733, TO_DATE('12/12/2018','MM/DD/YYYY') FROM DUAL UNION ALL
SELECT 14555, TO_DATE('07/23/2018','MM/DD/YYYY') FROM DUAL UNION ALL
SELECT 14555, TO_DATE('09/23/2018','MM/DD/YYYY') FROM DUAL
), sorted_results (po, requesteddate, receiveddate, rn)
AS
(
select t1.po,
t1.requesteddate,
t2.receiveddate,
row_number() over (partition by t1.po order by ABS(t1.requesteddate - t2.receiveddate)) as rn
from table1 t1 JOIN table2 t2 ON t1.po = t2.po
)
SELECT po, requesteddate, receiveddate as nearestreceiveddate FROM sorted_results WHERE rn = 1;
PO REQUESTEDDA NEARESTRECE
---------- ----------- -----------
14555 12-MAY-2018 23-JUL-2018
14733 12-FEB-2018 12-DEC-2018
14888 12-JAN-2018 11-JAN-2018
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/466328.html