我有兩張桌子。
REJECT_REASONS
| 戶口號碼 | Resn_Id1 | Resn_Id2 | Resn_Id3 | Resn_Id4 |
|---|---|---|---|---|
| 100 | 0001 | 0005 | 0006 | 0104 |
| 101 | 0005 | 0006 | 0104 | |
| 102 | 0001 | 0006 | ||
| 103 | ||||
| 104 | 0001 |
REASON_DESC
| Resn_Id | Resn_Desc |
|---|---|
| 0001 | 壞賬 |
| 0005 | 重復帳戶 |
| 0006 | 無效資料 |
| 0104 | 無效地址 |
我想將表 REJECT_REASONS 與 REASON_DESC 連接到 resn_id1、resn_id2、resn_id3 和 resn_id4 列的每一列上，并在結果中獲得 resn_desc。
我嘗試了以下查詢：
select r.account_no,r.resn_id1,rd1.resn_desc,
r.resn_id2,rd2.resn_desc,
r.resn_id3,rd3.resn_desc,
r.resn_id4,rd4.resn_desc
from reject_reasons r, reason_desc rd1, reason_desc rd2, reason_desc rd3, reason_desc rd4
where r.resn_id1=rd1.resn_id( )
and r.resn_id2=rd2.resn_id( )
and r.resn_id3=rd3.resn_id( )
and r.resn_id4=rd4.resn_id( )
;
所需的輸出：|Account_no | Resn_Id1 | Resn_desc1 | Resn_Id2 | Resn_desc2 | Resn_Id3 | Resn_desc3 | Resn_Id4 | Resn_desc4 |
請讓我知道是否有辦法簡化查詢。
uj5u.com熱心網友回復：
我猜你想要這樣的東西
with reject_reasons ( Account_No , Resn_Id1 , Resn_Id2 , Resn_Id3 , Resn_Id4 )
as
(
select 100, '0001' , '0005' , '0006' , '0104' from dual union all
select 101, '0005' , '0006' , '0104' , null from dual union all
select 102, '0001' , '0006' , null , null from dual union all
select 103, null , null , null , null from dual union all
select 104, '0001' , null , null , null from dual
),
reason_desc ( Resn_Id , Resn_Desc )
as
(
select '0001' , 'Bad Account' from dual union all
select '0005' , 'Duplicate Account' from dual union all
select '0006' , 'Invalid Data' from dual union all
select '0104' , 'Invalid address' from dual
)
select r.account_no,r.resn_id1,rd1.resn_desc,
r.resn_id2,rd2.resn_desc,
r.resn_id3,rd3.resn_desc,
r.resn_id4,rd4.resn_desc
from reject_reasons r
left join reason_desc rd1 on r.resn_id1=rd1.resn_id
left join reason_desc rd2 on r.resn_id2=rd2.resn_id
left join reason_desc rd3 on r.resn_id3=rd3.resn_id
left join reason_desc rd4 on r.resn_id4=rd4.resn_id
order by 1 ;
這里有一個演示
資料庫<>小提琴
uj5u.com熱心網友回復：
由于沒有所需的輸出，我認為另一種方法可以連接描述。
WITH REJECTION AS (SELECT DISTINCT *
FROM(
SELECT
Account_No,
Resn_Id1 AS Resn_Id
FROM reject_reasons r
UNION
SELECT
Account_No,
Resn_Id2 AS Resn_Id
FROM reject_reasons r
UNION
SELECT
Account_No,
Resn_Id3 AS Resn_Id
FROM reject_reasons r
UNION
SELECT
Account_No,
Resn_Id4 AS Resn_Id
FROM reject_reasons r))
SELECT REJECTION.Account_No,
listagg(rdesc.Resn_Desc, ', ') WITHIN GROUP (ORDER BY rdesc.Resn_Desc)
FROM REJECTION
LEFT JOIN reason_desc rdesc
ON rdesc.Resn_Id = REJECTION.Resn_Id
GROUP BY REJECTION.Account_No
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/334062.html