再发贴，多加分，请高手指点。。。

Question

有两个表a(a,b,c,d) b(a,b,c,d)a(a,b,c,d)
1 2 10 4
1 2 15 3
1 2 4 4a(a,b,c,d)
1 2 11 3
1 2 14 5
1 2 9 5我要连接成c表1 2 10 4 1 2 9 5
1 2 15 3 1 2 11 5
1 2 4 4
         1 2 14 5连接的条件是a.a=b.a and a.b=b.b and abs(a.c-b.c)<5 and abs(a.d-b.d)<5
但是我要求，a,b两表中，同时满足这个条件的记录有多条时，就按c ,d 两字段排序，
然后，再一对一匹配，
如上，a 表中两条与 b表中三条都能匹配上，
但是这里只要求对应出二条，还有一条就不匹配，对空就行了。请大家指点

Answer 1

原贴：http://topic.csdn.net/u/20101109/11/04142d6e-50af-4bc7-8a91-03932b499f90.html?60577

Answer 2

先處理掉重複，再想辦法處理不匹配drop table a;
create table a (a number, b number, c number, d number);drop table b;
create table b (a number, b number, c number, d number);insert into a values (1, 2, 10, 4);
insert into a values (1, 2, 15, 3);
insert into a values (1, 2, 4, 4);insert into b values (1, 2, 11, 3);
insert into b values (1, 2, 14, 5);
insert into b values (1, 2, 9, 5);commit;select a1,b1,c1,d1,a2,b2,c2,d2 from (
select A.a a1, A.b b1, a.c c1, a.d d1,
b.a a2, b.b b2, b.c c2, b.d d2,
row_number() over (partition by a.a, a.b, a.c, a.d order by b.c, b.d) rn
from a,b
where a.a=b.a and a.b=b.b and abs(a.c(+)-b.c)<5 and abs(a.d(+)-b.d)<5
)
where rn = 1;   A1    B1    C1    D1    A2    B2    C2    D2
----- ----- ----- ----- ----- ----- ----- -----
    1     2    10     4     1     2     9     5
    1     2    15     3     1     2    11     3

Answer 3

我要两表横着合成一张表，
虽然 连接的条件是a.a=b.a and a.b=b.b and abs(a.c-b.c)<5 and abs(a.d-b.d)<5，
可以会使得，左边表的一条记录，匹配上右表的几条记录，
或者右表的一条记录，能匹配上左边的几条记录，
但是我只要匹配上一条即可，这一条匹配完了，就不能再和其他的匹配了。如上数据：
a 表
1 2 10 4
1 2 15 3
能与b 表
1 2 11 3
1 2 14 5
1 2 9 5
都能匹配上，
但是通过对所有字段排序
所以，
1 2 10 4  对应  1 2 9 5
1 2 15 3  对应  1 2 11 3
空        对应  1 2 14 5如果只是直接的full outer join,
则会产生2*3=6条记录，不合我们的要求

Answer 4

最后一行，是csdn的问题，应该是前面为四字段为空，后面4个字段有值

Answer 5

这个有点意思，但是还是不能满足我的要求，
我做一个很接近答案的，但是还是有问题select aa.a,aa.b,aa.c,aa.d,bb.a,bb.b,bb.c,bb.d
from 
(select test_a.*,row_number() over(partition by a,b order by c asc,d asc) rn from test_a)aa 
 full outer join 
(select test_b.*,row_number() over(partition by a,b order by c asc,d asc) rn from test_b)bb 
on aa.a=bb.a and aa.b=bb.b 
and abs(aa.c-bb.c)<5 and abs(aa.d-bb.d)<5 and aa.rn=bb.rn;问题在于，aa.rn=bb.rn,
因为rn 的产生是 partition by a,b ，里面的分区不满足 abs(aa.c-bb.c)<5 and abs(aa.d-bb.d)<5 条件

Answer 6

SELECT ca1,
       cb1,
       cc1,
       cd1,
       ca2,
       cb2,
       cc2,
       cd2
  FROM (SELECT ca1,
               cb1,
               cc1,
               cd1,
               ca2,
               cb2,
               cc2,
               cd2,
               ROW_NUMBER ()
                  OVER (PARTITION BY ca1, cb1, cc1, cd1 ORDER BY cc2, cd2)
                  rn
          FROM (SELECT a1 ca1,
                       b1 cb1,
                       c1 cc1,
                       d1 cd1,
                       (CASE WHEN dc < 5 AND dd < 5 THEN a2 ELSE NULL END)
                          ca2,
                       (CASE WHEN dc < 5 AND dd < 5 THEN b2 ELSE NULL END)
                          cb2,
                       (CASE WHEN dc < 5 AND dd < 5 THEN c2 ELSE NULL END)
                          cc2,
                       (CASE WHEN dc < 5 AND dd < 5 THEN d2 ELSE NULL END)
                          cd2
                  FROM (SELECT A.a a1,
                               A.b b1,
                               a.c c1,
                               a.d d1,
                               b.a a2,
                               b.b b2,
                               b.c c2,
                               b.d d2,
                               ABS (a.c - b.c) dc,
                               ABS (a.d - b.d) dd
                          FROM a, b
                         WHERE a.a = b.a AND a.b = b.b)))
 WHERE rn = 1
UNION
SELECT ca1,
       cb1,
       cc1,
       cd1,
       ca2,
       cb2,
       cc2,
       cd2
  FROM (SELECT ca1,
               cb1,
               cc1,
               cd1,
               ca2,
               cb2,
               cc2,
               cd2,
               ROW_NUMBER ()
                  OVER (PARTITION BY ca1, cb1, cc1, cd1 ORDER BY cc2, cd2)
                  rn
          FROM (SELECT (CASE WHEN dc < 5 AND dd < 5 THEN a1 ELSE NULL END)
                          ca1,
                       (CASE WHEN dc < 5 AND dd < 5 THEN b1 ELSE NULL END)
                          cb1,
                       (CASE WHEN dc < 5 AND dd < 5 THEN c1 ELSE NULL END)
                          cc1,
                       (CASE WHEN dc < 5 AND dd < 5 THEN d1 ELSE NULL END)
                          cd1,
                       a2 ca2,
                       b2 cb2,
                       c2 cc2,
                       d2 cd2
                  FROM (SELECT A.a a1,
                               A.b b1,
                               a.c c1,
                               a.d d1,
                               b.a a2,
                               b.b b2,
                               b.c c2,
                               b.d d2,
                               ABS (a.c - b.c) dc,
                               ABS (a.d - b.d) dd
                          FROM a, b
                         WHERE a.a = b.a AND a.b = b.b)))
 WHERE rn = 1;  CA1   CB1   CC1   CD1   CA2   CB2   CC2   CD2
----- ----- ----- ----- ----- ----- ----- -----
    1     2     4     4                        
    1     2    10     4     1     2     9     5
    1     2    15     3     1     2    11     3
                            1     2     9     5希望樓主要求

Answer 7

  CA1   CB1   CC1   CD1   CA2   CB2   CC2   CD2
----- ----- ----- ----- ----- ----- ----- -----
    1     2     4     4                        
    1     2    10     4     1     2     9     5
    1     2    15     3     1     2    11     3
                            1     2     9     5谢谢nGX20080110，结果还是有点不对啊。。
最后一个不是1     2     9     5
而是：1   2   14   5

Answer 8

用存储过程实现也行，，而且这些条件和表，到时候都是参数，这条sql要拼出来才行的。。不能写死了。。

Answer 9

修改了一下SELECT ca1,
       cb1,
       cc1,
       cd1,
       ca2,
       cb2,
       cc2,
       cd2
  FROM (SELECT ca1,
               cb1,
               cc1,
               cd1,
               ca2,
               cb2,
               cc2,
               cd2,
               ROW_NUMBER ()
                  OVER (PARTITION BY ca1, cb1, cc1, cd1 ORDER BY cc2, cd2)
                  rn
          FROM (SELECT a1 ca1,
                       b1 cb1,
                       c1 cc1,
                       d1 cd1,
                       (CASE WHEN dc < 5 AND dd < 5 THEN a2 ELSE NULL END)
                          ca2,
                       (CASE WHEN dc < 5 AND dd < 5 THEN b2 ELSE NULL END)
                          cb2,
                       (CASE WHEN dc < 5 AND dd < 5 THEN c2 ELSE NULL END)
                          cc2,
                       (CASE WHEN dc < 5 AND dd < 5 THEN d2 ELSE NULL END)
                          cd2
                  FROM (SELECT A.a a1,
                               A.b b1,
                               a.c c1,
                               a.d d1,
                               b.a a2,
                               b.b b2,
                               b.c c2,
                               b.d d2,
                               ABS (a.c - b.c) dc,
                               ABS (a.d - b.d) dd
                          FROM a, b
                         WHERE a.a = b.a AND a.b = b.b)))
 WHERE (rn = 1  and ca2 is not null) or
 (ca2 is null and (ca1,cb1,cc1,cd1) not in (
 select a1,b1,c1,d1 from (
select A.a a1, A.b b1, a.c c1, a.d d1,
b.a a2, b.b b2, b.c c2, b.d d2,
row_number() over (partition by a.a, a.b, a.c, a.d order by b.c, b.d) rn
from a,b
where a.a=b.a and a.b=b.b and abs(a.c(+)-b.c)<5 and abs(a.d(+)-b.d)<5
)
where rn = 1
 )
 )
 
 union 
 SELECT ca1,
       cb1,
       cc1,
       cd1,
       ca2,
       cb2,
       cc2,
       cd2
  FROM (SELECT ca1,
               cb1,
               cc1,
               cd1,
               ca2,
               cb2,
               cc2,
               cd2,
               ROW_NUMBER ()
                  OVER (PARTITION BY ca1, cb1, cc1, cd1 ORDER BY cc2, cd2)
                  rn
          FROM (SELECT (CASE WHEN dc < 5 AND dd < 5 THEN a1 ELSE NULL END)
                          ca1,
                       (CASE WHEN dc < 5 AND dd < 5 THEN b1 ELSE NULL END)
                          cb1,
                       (CASE WHEN dc < 5 AND dd < 5 THEN c1 ELSE NULL END)
                          cc1,
                       (CASE WHEN dc < 5 AND dd < 5 THEN d1 ELSE NULL END)
                          cd1,
                       a2 ca2,
                       b2 cb2,
                       c2 cc2,
                       d2 cd2
                  FROM (SELECT A.a a1,
                               A.b b1,
                               a.c c1,
                               a.d d1,
                               b.a a2,
                               b.b b2,
                               b.c c2,
                               b.d d2,
                               ABS (a.c - b.c) dc,
                               ABS (a.d - b.d) dd
                          FROM a, b
                         WHERE a.a = b.a AND a.b = b.b)))
 WHERE (rn = 1 and ca1 is not null) or
 (ca1 is null and (ca2,cb2,cc2,cd2) not in (
 select a2,b2,c2,d2 from (
select A.a a1, A.b b1, a.c c1, a.d d1,
b.a a2, b.b b2, b.c c2, b.d d2,
row_number() over (partition by a.a, a.b, a.c, a.d order by b.c, b.d) rn
from a,b
where a.a=b.a and a.b=b.b and abs(a.c(+)-b.c)<5 and abs(a.d(+)-b.d)<5
)
where rn = 1
 )
 );  CA1   CB1   CC1   CD1   CA2   CB2   CC2   CD2
----- ----- ----- ----- ----- ----- ----- -----
    1     2     4     4                        
    1     2    10     4     1     2     9     5
    1     2    15     3     1     2    11     3
                            1     2    14     5

Answer 10

分析函数加full outer join

Answer 11

对，这个时候，a的记录按非=条件字段进行排序，
取最上的记录与b的记录匹配，
那a 的另一条记录则匹配空

Answer 12

还是用程序控制吧.太复杂,用sql实现不现实.

Answer 13

我做一个很接近答案的，但是还是有问题select aa.a,aa.b,aa.c,aa.d,bb.a,bb.b,bb.c,bb.d
from 
(select test_a.*,row_number() over(partition by a,b order by c asc,d asc) rn from test_a)aa 
 full outer join 
(select test_b.*,row_number() over(partition by a,b order by c asc,d asc) rn from test_b)bb 
on aa.a=bb.a and aa.b=bb.b 
and abs(aa.c-bb.c)<5 and abs(aa.d-bb.d)<5 and aa.rn=bb.rn;问题在于，aa.rn=bb.rn,
因为rn 的产生是 partition by a,b ，里面的分区不满足 abs(aa.c-bb.c)<5 and abs(aa.d-bb.d)<5 条件如果over()里能加入非 等于的条件就可以解决了

Answer 14

可行啊.sql的问题在于你的条件不仅仅是a和b之间的关系,a表中数据间还有排斥关系.最好是些存储过程,使用cursor,再建一个结果表进行比较.遍历完cursor就可以得到结果了.

Answer 15

原来，我打算动态来拼sql，现在不行了，
但是又回来上次的问题上了，
存储过程就不太好拼了，
这里的表，字段，条件都要作为参数传入，
不知能不能搞定。 

Answer 16

那问题又回到，这个贴子了。。
http://topic.csdn.net/u/20101028/20/8264c578-0aa4-4448-ac8c-774da1c370af.html

Answer 17

麻烦minitoy帮我写个框，谢谢了。。

Answer 18

SQL> create table c
  2  (aa number,
  3   ab number,
  4   ac number,
  5   ad number,
  6   ba number,
  7   bb number,
  8   bc number,
  9   bd number)
 10  ;
 
Table created
 
SQL> 
SQL> create or replace procedure proc_match_data as
  2    cursor cv_a is
  3      select a, b, c, d from a order by a, b, c, d;
  4    v_a   number;
  5    v_b   number;
  6    v_c   number;
  7    v_d   number;
  8    v_num number;
  9  begin
 10    open cv_a;
 11    loop
 12      fetch cv_a
 13        into v_a, v_b, v_c, v_d;
 14      exit when cv_a%notfound;
 15      select count(*)
 16        into v_num
 17        from b
 18       where b.a = v_a
 19         and b.b = v_b
 20         and abs(v_c - b.c) < 5
 21         and abs(v_d - b.d) < 5
 22         and not exists (select 1
 23                from c
 24               where c.ba = b.a
 25                 and c.bb = b.b
 26                 and c.bc = b.c
 27                 and c.bd = b.d);
 28      if v_num = 0 then
 29        insert into c (aa, ab, ac, ad) values (v_a, v_b, v_c, v_d);
 30      else
 31        insert into c
 32          select v_a, v_b, v_c, v_d, t.a, t.b, t.c, t.d
 33            from (select b.a,
 34                         b.b,
 35                         b.c,
 36                         b.d,
 37                         row_number() over(order by b.c, b.d) rn
 38                    from b
 39                   where b.a = v_a
 40                     and b.b = v_b
 41                     and abs(v_c - b.c) < 5
 42                     and abs(v_d - b.d) < 5
 43                     and not exists (select 1
 44                            from c
 45                           where c.ba = b.a
 46                             and c.bb = b.b
 47                             and c.bc = b.c
 48                             and c.bd = b.d)) t
 49           where rn = 1;
 50      end if;
 51  
 52    end loop;
 53    insert into c
 54      (ba, bb, bc, bd)
 55      select a, b, c, d
 56        from b
 57       where not exists (select 1
 58                from c
 59               where c.ba = b.a
 60                 and c.bb = b.b
 61                 and c.bc = b.c
 62                 and c.bd = b.d);
 63  end;
 64  /
 
Procedure created
 
SQL> exec proc_match_data;
 
PL/SQL procedure successfully completed
 
SQL> select * from c;
 
        AA         AB         AC         AD         BA         BB         BC         BD
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          2          4          4                                  
         1          2         10          4          1          2          9          5
         1          2         15          3          1          2         11          3
                                                     1          2         14          5
 
SQL> select * from a;
 
         A          B          C          D
---------- ---------- ---------- ----------
         1          2         10          4
         1          2         15          3
         1          2          4          4
 
SQL> select * from b;
 
         A          B          C          D
---------- ---------- ---------- ----------
         1          2         11          3
         1          2         14          5
         1          2          9          5
 
SQL> 

Answer 19

搞好了得请 minitoy 吃饭呵呵

Answer 20

总结一下：为了这个问题，搞了好几天，总以为sql可以搞定所有的事，结果却不是，或很难搞定。
讨论到现在，其实我们可以换个思路，数据库与程序的结合，解决这个问题就容易了。人啊，有的时候就是转不过弯来，总喜欢强求。谢谢各位的回答，光棍节快乐。

Answer 21

恩,sql做太精细的控制不合适,

Answer 22

排序匹配.关键是楼主引入了abs那两个条件.问题就复杂了.

Answer 23

对如果没有abs那两个条件，那用over就直接解决了，
所以，后来，我认为如果能在over 里的partition里加入 abs的那两个条件就容易解决了。

Answer 24

欢迎 gelyon 来谈谈思路

Answer 25

语文没学好paddy～～嘎嘎！！

Answer 26

with a1 as (select cnt, a, b, c, d from (select row_number() over( order by c, d) as cnt, a, b, c, d 
                from a ) t1 ),
     b1 as (select cnt, a, b, c, d from (select row_number() over( order by c, d) as cnt, a, b, c, d
                from b ) t2 )
select a1.a, a1.b, a1.c, a1.d, b1.a, b1.b, b1.c, b1.d
from a1 left join b1 on a1.cnt=b1.cnt and a1.a=b1.a and a1.b=b1.b and abs(a1.c-b1.c)<5 and abs(a1.d-b1.d)<5
union 
select a1.a, a1.b, a1.c, a1.d, b1.a, b1.b, b1.c, b1.d
from a1 right join b1 on b1.cnt=a1.cnt and b1.a=a1.a and b1.b=a1.b and abs(b1.c-a1.c)<5 and abs(b1.d-a1.d)<5;