现有表A和表B,字段结构一样,数据不一样。
A表字段数据如下所示
code value
a 1
a 2
a 3
……
B表字段数据如下所示
code value
b 3
b 4
b 5
……
现在我想根据code分组,遍历组中的value值,连接A表和B表查询只要a组和b组中有一个value值相同(比如3),那么我就把其中所有这俩组中全部的6条数据赋予一个代码(即俩表中增加一个字段,通过这个代码能把俩表中有关系的数据都查询出来),求思路。
A表字段数据如下所示
code value
a 1
a 2
a 3
……
B表字段数据如下所示
code value
b 3
b 4
b 5
……
现在我想根据code分组,遍历组中的value值,连接A表和B表查询只要a组和b组中有一个value值相同(比如3),那么我就把其中所有这俩组中全部的6条数据赋予一个代码(即俩表中增加一个字段,通过这个代码能把俩表中有关系的数据都查询出来),求思路。
不考虑这个的话可以先让两个表根据value做inner join,然后根据匹配的上记录的code来给同组的添加代码。
select 'a' code, 1 value from dual union all
select 'a' code, 2 value from dual union all
select 'a' code, 3 value from dual),b as(
select 'b' code, 3 value from dual union all
select 'b' code, 4 value from dual union all
select 'b' code, 5 value from dual),
t as(
SELECT a.code acode, b.code bcode FROM a, b WHERE a.value = b.value)
SELECT *
FROM a
WHERE EXISTS (SELECT 1 FROM t WHERE a.code = t.acode)
UNION ALL
SELECT * FROM b WHERE EXISTS (SELECT 1 FROM t WHERE b.code = t.bcode)
with a as (
select 'a' code,1 val from dual union all
select 'a',2 from dual union all
select 'a',3 from dual union all
select 'e',10 from dual union all
select 'f',9 from dual union all
select 'p',45 from dual)
,b as(
select 'b' code,3 val from dual union all
select 'b',4 from dual union all
select 'b',5 from dual union all
select 'c',15 from dual union all
select 'm',9 from dual)
select *
from a
where a.code in(
select a.code
from a,b
where a.val=b.val)
union all
select *
from b
where b.code in(
select b.code
from a,b
where a.val=b.val)
/CODE VAL
---- ----------
a 3
a 2
a 1
f 9
b 5
b 4
b 3
m 98 rows selected
select 'a' code, 1 value from dual union all
select 'a' code, 2 value from dual union all
select 'a' code, 3 value from dual),b as(
select 'b' code, 3 value from dual union all
select 'b' code, 4 value from dual union all
select 'b' code, 5 value from dual),
t as(
SELECT a.code acode, b.code bcode
FROM a, b WHERE a.value = b.value)
SELECT *
FROM a
WHERE EXISTS
(SELECT 1 FROM t WHERE a.code = t.acode)
UNION ALL
SELECT * FROM b WHERE EXISTS (SELECT 1 FROM t WHERE b.code = t.bcode)