sql - 一对一的选择限制

Question

我遇到过这样的问题。有两个表(x 值是有序的，因此呈递增趋势!)

表A

    id     x
    1      1
    1      3
    1      4
    1      7

表B

    id     x
    1      2
    1      5

我想加入这两个表:
1)在id和
相等的条件下2)A的每一行应该只与B的一行匹配，反之亦然(一对一关系)基于x值的绝对差(小差行有更优先匹配)。

仅根据上面的描述并不能清楚描述，因为如果其中一个表中共享同一行的两对行具有相同的差异，则无法确定先行。所以将A定义为“主”表，A表中行号较小的行总是先行

演示的预期结果:

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

表格结束(不应该考虑 A 中的额外两行，因为一对一的规则)

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

    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 中似乎很棘手。

Answer 1

尝试:

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

fiddle :http://sqlfiddle.com/#!15/ab5ae/5/0

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

编辑 - 根据 a 到 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

fiddle :http://sqlfiddle.com/#!15/ab5ae/19/0