我有一張這樣的桌子
TRX_NUMBER 是一張發票，這個欄位在發票里面有一個退貨號。
我想選擇表并加入同一個表，使用 CUSTOMER_TRX_ID 和 PREVIOUS_CUSTOMER_TRX_ID 作為連接（ON）
結果就是我想要的
你能幫我嗎？
uj5u.com熱心網友回復：
這是一個選項：
樣本資料：
SQL> with invoice (customer_trx_id, trx_number, previous_customer_trx_id) as
2 (select 81196, 'ARR05-09', 22089 from dual union all
3 select 22089, 'IJU86-09', null from dual union all
4 select 13931, 'IJU07-09', null from dual
5 )
查詢從這里開始：
6 select a.trx_number, b.trx_number as retur
7 from invoice a left join invoice b on a.customer_trx_id = b.previous_customer_trx_id
8 where not exists (select null
9 from invoice c
10 where c.customer_trx_id = a.previous_customer_trx_id);
TRX_NUMBER RETUR
--------------- --------
IJU86-09 ARR05-09
IJU07-09
SQL>
uj5u.com熱心網友回復：
使用別名多次獲取同一張表。
例如我們有一個表 INVOICE：
SELECT t1.TRX_NUMBER AS TRX_NUMBER, t2.TRX_NUMBER AS RETUR
FROM INVOICE t1
LEFT JOIN INVOICE t2 ON t1.CUSTOMER_TRX_ID = t2.PREVIOUS_CUSTOMER_TRX_ID
如果只有一個級別，則添加一個條件
where not exists (select null
from invoice t3
where t3.customer_trx_id = t1.previous_customer_trx_id)
或排除所有有以前數字的地方——這意味著它們已經低了一個級別
where t1.PREVIOUS_CUSTOMER_TRX_ID is null
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/533850.html
標籤：加入PLSQL