有两个表
TableA
---------------------------
CID PW1 PW2
1 A1 A2
2 A3 A4
3 A2 A5
========================
TableB
-----------------------
ID PW
1 A1
2 A2
3 A3
4 A4
5 A5
========================
输入一个PW,需要查询出相关PW
假如输入A1,查询结果为:
CID PW
1 A1
1 A2(TableA中 A1,A2为同一条纪录)
假如输入A2,查询结果为:
CID PW
1 A1
1 A2
3 A5
SELECT DISTINCT tb.ID,
tb.pw
FROM TableB tb,
TableA ta
WHERE (ta.pw1 = 'A2' OR ta.pw2 = 'A2')
AND tb.pw IN (ta.pw1, ta.pw2)
WITH TableA AS (
SELECT 1 cid,'A1' pw1,'A2' pw2 FROM DUAL UNION ALL
SELECT 2 cid,'A3' pw1,'A4' pw2 FROM DUAL UNION ALL
SELECT 3 cid,'A2' pw1,'A5' pw2 FROM DUAL
),
TableB AS (
SELECT 1 ID,'A1' pw FROM DUAL UNION ALL
SELECT 2 ID,'A2' pw FROM DUAL UNION ALL
SELECT 3 ID,'A3' pw FROM DUAL UNION ALL
SELECT 4 ID,'A4' pw FROM DUAL UNION ALL
SELECT 5 ID,'A5' pw FROM DUAL
)
SELECT m.cid,
m.pw
FROM (SELECT ta.cid,
tb.pw,
ROW_NUMBER() OVER(PARTITION BY tb.pw ORDER BY ta.cid) rn
FROM TableB tb,
TableA ta
WHERE (ta.pw1 = 'A2' OR ta.pw2 = 'A2')
AND tb.pw IN (ta.pw1, ta.pw2)) m
WHERE m.rn = 1
UNION
SELECT CID,PW2 FROM TableA WHERE PW2='A1'