17,382
社区成员




[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 INSTR(B.BH, A.BH) > 0);
I BH BBH
- ---------------------------------- -------
1 测试1-0001、测试2-0002 aa-0001
2 测试1-0001 aa-0002
3 测试1-0002、测试1-0003 aa-0003
4 测试1-0001、测试1-0002、测试2-0002 aa-0005
4 测试2-0001
6 测试2-0003
已选择6行。
[SYS@myoracle] SQL>
--不确定是不是理解你的意思了
select A.id, B.bh, B.bbh from A,B where instr(B.bh, A.bh)>0;
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(+)||'%'
--这不给你把剩下的 不在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;
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;
select id,bh,bbh from B
union all
select id,bh,null from A,B
where instr(B.bh, A.bh) = 0;