table1 主表
id name b1_id b2_id b3_id b4_id
1 a id1 id2 id3 id4
其中tableq 后四列 可能全有值，也可能只有其中某列有值
table2
id name
id1 name1
id2 name2
id3 name3
id3 name4
现在根据 table2 表的 一个或多个Name 模糊查询table1 并把table的后四列转成name
table1 主表
id name b1_id b2_id b3_id b4_id
1 a name1 name2 name3 name4
数据量大 ，不好用IN 求各位高手
------解决思路----------------------
SELECT T.ID,
T.NAME,
(SELECT T1.NAME FROM TABLE2 T1 WHERE T1.ID = T.B1_ID) B1_ID,
(SELECT T1.NAME FROM TABLE2 T1 WHERE T1.ID = T.B2_ID) B2_ID,
(SELECT T1.NAME FROM TABLE2 T1 WHERE T1.ID = T.B3_ID) B3_ID,
(SELECT T1.NAME FROM TABLE2 T1 WHERE T1.ID = T.B4_ID) B4_ID
FROM TABLE1 T
------解决思路----------------------
WITH table1 AS(
SELECT '1' ID,'a' NAME,'id1' b1_id,'id2' b2_id,'id3' b3_id,'id4' b4_id FROM dual),
table2 AS(
SELECT 'id1' ID,'name1' NAME FROM dual UNION ALL
SELECT 'id2' ID,'name2' NAME FROM dual UNION ALL
SELECT 'id3' ID,'name3' NAME FROM dual UNION ALL
SELECT 'id4' ID,'name4' NAME FROM dual
)
SELECT t1.ID,t2.NAME b1_id,t3.NAME b2_id,t4.NAME b3_id,t5.NAME b4_id
FROM TABLE1 T1, TABLE2 T2, TABLE2 T3, TABLE2 T4, TABLE2 T5
WHERE T1.B1_ID = T2.ID(+)
AND T1.B2_ID = T3.ID(+)
AND T1.B3_ID = T4.ID(+)
AND T1.B4_ID = T5.ID(+)