--不确定是不是理解你的意思了
select A.id, B.bh, B.bbh from A,B where instr(B.bh, A.bh)>0;

------解决方案--------------------
此奥，我问你：
测试1-0001  在B表里存在两条，我勒个去的，你根据什么规则确定两条数据都要显示的？
需求混乱的要命。。

------解决方案--------------------
SQL code

select id,bh,bbh from B
union all
select id,bh,null from A,B
where instr(B.bh, A.bh) = 0;

------解决方案--------------------
SQL code

select id,bh,bbh from B
union all
select A.id,A.bh,null from A,B --这个就是查询A表的同学 上面忘记写表别名了。
where instr(B.bh, A.bh) = 0;

------解决方案--------------------
SQL code

--这不给你把剩下的 不在B里的(instr(B.bh, A.bh) = 0)给你union 上了么。。。。

union all
select A.id,A.bh,null from A,B where instr(B.bh, A.bh) = 0;

------解决方案--------------------
SQL code

WITH a AS
(SELECT '1' ID ,'测试1-0001' bh,'测试1' NAME, '2012-01-01' rq FROM dual
UNION ALL
SELECT '2' ID ,'测试1-0002' bh,'测试1' NAME, '2012-01-01' rq FROM dual
UNION ALL 
SELECT '3' ID ,'测试1-0003' bh,'测试1' NAME, '2012-01-01' rq FROM dual
),
b AS
(
  SELECT '1' ID ,'aa-0001' bbh,'测试1-0001、测试2-0002' bh, '2012-01-01' rq FROM dual
  UNION ALL
  SELECT '2' ID ,'aa-0002' bbh,'测试1-0001' bh, '2012-01-01' rq FROM dual
)
SELECT * FROM a,b
WHERE a.bh LIKE '%'||b.bh(+)||'%'

------解决方案--------------------

SQL code

[SYS@myoracle] SQL>WITH a AS
  2  (SELECT '1' ID ,'测试1-0001' bh,'测试1' NAME, '2012-01-01' rq FROM dual
  3  UNION ALL
  4  SELECT '2' ID ,'测试1-0002' bh,'测试1' NAME, '2012-01-01' rq FROM dual
  5  UNION ALL
  6  SELECT '3' ID ,'测试1-0003' bh,'测试1' NAME, '2012-01-01' rq FROM dual
  7  UNION ALL
  8  SELECT '4' ID ,'测试2-0001' bh,'测试2' NAME, '2012-01-01' rq FROM dual
  9  UNION ALL
 10  SELECT '5' ID ,'测试2-0002' bh,'测试2' NAME, '2012-01-01' rq FROM dual
 11  UNION ALL
 12  SELECT '6' ID ,'测试2-0003' bh,'测试2' NAME, '2012-01-01' rq FROM dual
 13  ),
 14  b AS
 15  (
 16    SELECT '1' ID ,'aa-0001' bbh,'测试1-0001、测试2-0002' bh, '2012-01-01' rq FROM dual
 17    UNION ALL
 18    SELECT '2' ID ,'aa-0002' bbh,'测试1-0001' bh, '2012-01-01' rq FROM dual
 19    UNION ALL
 20    SELECT '3' ID ,'aa-0003' bbh,'测试1-0002、测试1-0003' bh, '2012-01-01' rq FROM dual
 21    UNION ALL
 22    SELECT '4' ID ,'aa-0005' bbh,'测试1-0001、测试1-0002、测试2-0002' bh, '2012-01-01' rq FROM dual
 23  )
 24  SELECT ID, BH, BBH
 25    FROM B
 26  UNION ALL
 27  SELECT ID, BH, NULL
 28    FROM A
 29   WHERE A.ID NOT IN (SELECT A.ID FROM A, B WHERE I