一对一的选择限制 [英] one to one distinct restriction on selection

问题描述

我遇到了类似的问题。有两个表（x值的排序使
呈递增趋势！）

I encountered a problem like that. There are two tables (x value is ordered so that in a incremental trend !)

表A

    id     x
    1      1
    1      3
    1      4
    1      7

表B

    id     x
    1      2
    1      5

我想加入这两个表：

1）在id和

相等的条件下2）A的每一行应仅与B的一行匹配，反之（基于x的绝对差）（一对一关系）值（差异较小的行具有
的更高优先级进行匹配）。

I want to join these two tables:
1) on the condition of the equality of id and
2) each row of A should be matched only to one row of B, vice verse (one to one relationship) based on the absolute difference of x value (small difference row has more priority to match).

仅基于上面的描述并不清楚，因为如果两对行共享一个表中的同一行有相同的区别，没有办法确定哪个先行。因此，将A定义为主表，表A中行号较小的行始终排在最前面

Only based on the description above it is not a clear description because if two pairs of row which share a common row in one of the table have the same difference, there is no way to decide which one goes first. So define A as "Main" table, the row in table A with smaller line number always go first

演示的预期结果：

    id      A.x     B.x    abs_diff
    1       1       2      1
    1       4       5      1

表尾（由于一对一的规则，不应考虑A中的额外两行）

End of table(two extra rows in A shouldn't be considered, because one to one rule)

我正在使用PostgreSQL，所以我尝试过的事情是DISTINCT ON，但无法解决。

I am using PostgreSQL so the thing I have tried is DISTINCT ON, but it can not solve.

    select distinct on (A.x) id,A.x,B.x,abs_diff
    from
    (A join B
    on A.id=B.id)
    order by A.x,greatest(A.x,B.x)-least(A.x,B.x)

您有任何想法吗，在普通SQL中似乎很棘手。

Do you have any ideas, it seems to be tricky in plain SQL.

推荐答案

尝试：

select a.id, a.x as ax, b.x as bx, x.min_abs_diff
  from table_a a
  join table_b b
    on a.id = b.id
  join (select a.id, min(abs(a.x - b.x)) as min_abs_diff
          from table_a a
          join table_b b
            on a.id = b.id
         group by a.id) x
    on x.id = a.id
   and abs(a.x - b.x) = x.min_abs_diff

小提琴： http://sqlfiddle.com/#!15/ab5ae/5/0

尽管它与您的预期输出不匹配，但根据您的描述，我认为输出是正确的，因为您可以看到每对都有绝对的差异值1。

Although it doesn't match your expected output, I think the output is correct based on what you described, as you can see each pair has a difference with an absolute value of 1.

编辑-根据a到b的顺序尝试以下操作：

Edit - Try the following, based on order of a to b:

select *
  from (select a.id,
               a.x as ax,
               b.x as bx,
               x.min_abs_diff,
               row_number() over(partition by a.id, b.x order by a.id, a.x) as rn
          from table_a a
          join table_b b
            on a.id = b.id
          join (select a.id, min(abs(a.x - b.x)) as min_abs_diff
                 from table_a a
                 join table_b b
                   on a.id = b.id
                group by a.id) x
            on x.id = a.id
           and abs(a.x - b.x) = x.min_abs_diff) x
 where x.rn = 1

提琴： http：// sqlfiddle .com /＃！15 / ab5ae / 19/0

这篇关于一对一的选择限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！