17,377
社区成员
发帖
与我相关
我的任务
分享
--desc 不能做列名
[SYS@myorcl] SQL>WITH A AS(
2 SELECT 1 ID,'10001' po_no, 'aaa' DESC1 FROM dual
3 UNION ALL
4 SELECT 2 ID,'10002' po_no, 'bbb' DESC1 FROM dual
5 UNION ALL
6 SELECT 3 ID,'10003' po_no, 'ccc' DESC1 FROM dual
7 UNION ALL
8 SELECT 4 ID,'10004' po_no, 'ddd' DESC1 FROM dual
9 UNION ALL
10 SELECT 5 ID,'10005' po_no, 'eee' DESC1 FROM dual
11 ),B AS(
12 SELECT 1 ID,'00001' inv_no,'10001' ref_po FROM dual
13 UNION ALL
14 SELECT 2 ID,'00002' inv_no,'10002,10003' ref_po FROM dual
15 UNION ALL
16 SELECT 3 ID,'00003' inv_no,'10004,10005' ref_po FROM dual
17 )SELECT B.id,b.inv_no,a.desc1
18 FROM A,b
19 WHERE instr(B.ref_po,a.po_no)>0;
ID INV_N DES
---------- ----- ---
1 00001 aaa
2 00002 bbb
2 00002 ccc
3 00003 ddd
3 00003 eee