sql - 通过选择最小差异加入多个列并在其中一个整数列中加入

Question

我得到了表 t1，我想在下面的 a、b 和 c 列上加入表 t2

+---------+---------+---------+
|a        |b        |c        |
+---------+---------+---------+
|473200   |1        |1.-1-1   |
|472400   |10       |1.-1-1   |
|472800   |10       |1.-1-1   |
|473200   |93       |1.-1-1   |
|472800   |26240    |1.-1-1   |
+---------+---------+---------+

t2

+---------+---------+---------+
|a        |b        |c        |
+---------+---------+---------+
|473200   |1        |1.-1-1   |
|472400   |10       |1.-1-1   |
|472800   |10       |1.-1-1   |
|473200   |93       |1.-1-1   |
|472800   |26250    |1.-1-1   |
+---------+---------+---------+

当我只加入 a 和 c 结果是

+---------+---------+---------+---------+
|t1.b     |t2.b     |a        |c        |
+---------+---------+---------+---------+
|93       |1        |473200   |1.-1-1   |
|1        |1        |473200   |1.-1-1   |
|10       |10       |472400   |1.-1-1   |
|10       |10       |472800   |1.-1-1   |
|26240    |10       |472800   |1.-1-1   |
|93       |93       |473200   |1.-1-1   |
|1        |93       |473200   |1.-1-1   |
|10       |26250    |472800   |1.-1-1   |
|26240    |26250    |472800   |1.-1-1   |
+---------+---------+---------+---------+

我试图实现的是将 b 列添加到“on”子句中，以便连接发生在 b 列的最小差异上。

期望的结果

+---------+---------+---------+---------+
|t1.b     |t2.b     |a        |c        |
+---------+---------+---------+---------+
|1        |1        |473200   |1.-1-1   |
|10       |10       |472400   |1.-1-1   |
|10       |10       |472800   |1.-1-1   |
|93       |93       |473200   |1.-1-1   |
|26240    |26250    |472800   |1.-1-1   |
+---------+---------+---------+---------+

我在这里看到了类似的东西

https://dba.stackexchange.com/questions/73804/how-to-retrieve-closest-value-based-on-look-up-table

但不知道如何申请我的案子。

标签： sqlpostgresqljoinsql-order-bygreatest-n-per-group

Answer 1

一种选择是横向连接：

select t1.*, t2.b b2
from t1
cross join lateral (
    select t2.*
    from t2
    where t2.a = t1.a and t2.c = t1.c
    order by abs(t2.b - t1.b)
    limit 1
)

另一种可能性是distinct on- 但您需要t1. 假设(a, c)元组唯一标识 中的每一行t1，你会去：

select distinct on (t1.a, t1.c) t1.*, t2.b b2
from t1
inner join t2 on t2.a = t1.a and t2.c = t1.c
order by t1.a, t1.c, abs(t2.b - t1.b)