我有一个需求,
A表
a b c
1 2 2
1 2 3
B表
a b c
1 2 1
1 2 4
如果通过下面的SQL,便成了两表的笛卡尔积了,会生成四表记录,
select * from a,b
where a.a=b.a and a.b=b.b and abs(a.c-b.c)<5;
但是我想得到:
a b c a b c
1 2 2 1 2 1
1 2 3 1 2 4
也就是说,我只要每一条记录匹配一条记录,
如果能匹配上多条,就匹配C最接近的那条,
如果没有匹配就留空。如何看sql呢 ,大家帮我想想。
A表
a b c
1 2 2
1 2 3
B表
a b c
1 2 1
1 2 4
如果通过下面的SQL,便成了两表的笛卡尔积了,会生成四表记录,
select * from a,b
where a.a=b.a and a.b=b.b and abs(a.c-b.c)<5;
但是我想得到:
a b c a b c
1 2 2 1 2 1
1 2 3 1 2 4
也就是说,我只要每一条记录匹配一条记录,
如果能匹配上多条,就匹配C最接近的那条,
如果没有匹配就留空。如何看sql呢 ,大家帮我想想。
因为我们也不知道调到多少可以只匹配出小于一条记录。
with a as
(
select 1 ca, 2 cb, 2 cc from dual union all
select 1 ca, 2 cb, 3 cc from dual),
b as(
select 1 ca, 2 cb, 1 cc from dual union all
select 1 ca, 2 cb, 4 cc from dual)
select a1,b1,c1,a2,b2,c2 from (
select a1,b1,c1,a2,b2,c2,row_number() over(partition by a1,b1,c1,a2,b2 order by fabs) rn from (
select a.ca a1,a.cb b1,a.cc c1,b.ca a2,b.cb b2,b.cc c2,abs(a.cc-b.cc) fabs
from a,b
where a.ca=b.ca and a.cb=b.cb and abs(a.cc-b.cc)<5
)
) where rn = 1; A1 B1 C1 A2 B2 C2
---------- ---------- ---------- ---------- ---------- ----------
1 2 2 1 2 1
1 2 3 1 2 4
(select a,b,c,row_number()over(partition by a,b order by c asc) rn from a)aa,
(select a,b,c,row_number()over(partition by a,b order by c asc) rn from b)bb
where aa.a=bb.a and aa.b=bb.b and aa.rn=bb.rn
SQL> with a as
2 (
3 select 1 a, 2 b, 2 c from dual union all
4 select 1 a, 2 b, 3 c from dual),
5 b as(
6 select 1 a, 2 b, 1 c from dual union all
7 select 1 a, 2 b, 4 c from dual)
8 select aa.a,aa.b,aa.c,bb.a,bb.b,bb.c from
9 (select a,b,c,row_number()over(partition by a,b order by c asc) rn from a)aa,
10 (select a,b,c,row_number()over(partition by a,b order by c asc) rn from b)bb
11 where aa.a=bb.a and aa.b=bb.b and aa.rn=bb.rn
12 ;
A B C A B C
---------- ---------- ---------- ---------- ---------- ----------
1 2 2 1 2 1
1 2 3 1 2 4
SQL> with a as
2 (
3 select 1 a, 2 b, 2 c from dual union all
4 select 1 a, 2 b, 3 c from dual),
5 b as(
6 select 1 a, 2 b, 1 c from dual union all
7 select 1 a, 2 b, 4 c from dual)
8 select aa.a,aa.b,aa.c,bb.a,bb.b,bb.c from
9 (select a,b,c,row_number()over(partition by a,b order by c asc) rn from a)aa full outer join
10 (select a,b,c,row_number()over(partition by a,b order by c asc) rn from b)bb
11 on aa.a=bb.a and aa.b=bb.b and aa.rn=bb.rn
12 ;
A B C A B C
---------- ---------- ---------- ---------- ---------- ----------
1 2 2 1 2 1
1 2 3 1 2 4
SQL>
SELECT 1 a, 2 b, 2 c FROM dual UNION ALL
SELECT 1, 2, 3 FROM dual UNION ALL
SELECT 1, 3, 4 FROM dual
),
B AS(
SELECT 1 a, 2 b,1 c FROM dual UNION ALL
SELECT 1, 4, 3 FROM dual UNION ALL
SELECT 1, 2, 4 FROM dual
)
select aa.a,aa.b,aa.c,bb.a,bb.b,bb.c from
(select a,b,c,row_number()over(partition by a,b order by c asc) rn from a)aa
full outer join
(select a,b,c,row_number()over(partition by a,b order by c asc) rn from b)bb
on aa.a=bb.a and aa.b=bb.b and aa.rn=bb.rn;a b c a b c
----------------
1 2 2 1 2 1
1 2 3 1 2 4
1 3 4
1 4 3
--完全外连接
11:46:38 SQL> create table tba as
11:46:48 2 with tb1 as
11:46:50 3 (
11:46:50 4 select 1 a,2 b,2 c from dual union all
11:46:50 5 select 1,2,3 from dual)
11:46:51 6 select * from tb1
11:46:58 7 /表已创建。11:47:53 SQL> create table tbb as
11:47:59 2 with tb2 as(
11:47:59 3 select 1 a, 2 b, 1 c from dual union all
11:47:59 4 select 1 , 2 , 4 from dual)
11:47:59 5 select * from tb2
11:47:59 6 /表已创建。11:48:00 SQL> select t.a,t.b,t.c,k.a,k.b,k.c
11:48:59 2 from (select a,b,c,rownum rn from tba ) t full join (select a,b,c,rownum rn from tbb )
k
11:48:59 3 on t.rn=k.rn
11:48:59 4 / A B C A B C
---------- ---------- ---------- ---------- ---------- ----------
1 2 2 1 2 1
1 2 3 1 2 4