选择两个值以及与前一个值具有最小距离的两个值 [英] select two values and the two values with which former values have minimum distance with

问题描述

我有三列

Key, x1,  y1
1    31   34
2    43   40
3    41   44
4    100  40

我的预期输出是:

Key, x1,  y2, closest_x1,  closest_y2
1    31   34     43          40
2    43   40     41          44
3    41   44     43          40
4    100  40     41          44 

有预期输出的最简单的 sql 查询是什么?请注意，在找出最接近的对时，同时考虑 x1,y1 两个值

what can be the simplest sql query to have the expected output? Note that both the values x1,y1 are considered while finding out the closest pair

推荐答案

我认为在您预期结果的第 4 行中，最接近的值是错误的.
应该是:

I think that in line 4 of your expected result the closest values are wrong.
It should be:

4   100 40  43  40

至少这是我得到的结果:

at least this is the result I get by this:

select t.*, tt.x1 closest_x1, tt.y1 closest_y1
from tablename t
inner join tablename tt
on tt.key = (
  select min(key) from tablename where power(x1 - t.x1, 2) + power(y1 - t.y1, 2) = (
    select min(power(x1 - t.x1, 2) + power(y1 - t.y1, 2)) from tablename where key <> t.key
  )
)
order by t.key

结果:

| key  | x1  | y1  | closest_x1 | closest_y1 |
| ---- | --- | --- | ---------- | ---------- |
| 1    | 31  | 34  | 43         | 40         |
| 2    | 43  | 40  | 41         | 44         |
| 3    | 41  | 44  | 43         | 40         |
| 4    | 100 | 40  | 43         | 40         |

这篇关于选择两个值以及与前一个值具有最小距离的两个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！