我的数据表里有点的平面坐标(x,y)，怎么用一个select语句来查找表里与已知点最接近的点
------解决方案--------------------
create table t_1
(x numeric(18,2),
y numeric(18,2)
)
insert into t_1 values(1,2)
insert into t_1 values(2,3)
insert into t_1 values(3,5)
insert into t_1 values(1,5)
insert into t_1 values(3,2)
select top 1 b.x, b.y
from t_1 a,
t_1 b
where a.x = 1
and a.y = 2
and (b.x <> a.x or b.y <> a.y)
order by (b.y-a.y)*(b.y-a.y) + (b.x-a.x)*(b.x-a.x)
drop table t_1
-----
x y
-------------------- --------------------
2.00 3.00
(1 row(s) affected)
------解决方案--------------------
create table t_1(x numeric(18,2),y numeric(18,2))
insert into t_1 values(1,2)
insert into t_1 values(2,3)
insert into t_1 values(3,5)
insert into t_1 values(1,5)
insert into t_1 values(3,2)
declare @x numeric(18,2),@y numeric(18,2)
select @x = 2,@y = 2
select top 1 *,power(([email protected])*([email protected])+([email protected])*([email protected]),0.5) as 距离
from t_1
order by 距离
drop table t_1
/*
x y 距离
-------------------- -------------------- ----------------------------------------
2.00 3.00 1.0000
(1 row(s) affected)
*/
------解决方案--------------------
借
freeliu()數據
create table t_1
(x numeric(18,2),
y numeric(18,2)
)
insert into t_1 values(1,2)
insert into t_1 values(2,3)
insert into t_1 values(3,5)
insert into t_1 values(1,5)
insert into t_1 values(3,2)
select top 1* from t_1 order by (sqrt((x-3)*(x-3)+(y-3)*(y-3)))
x y
-------------------- --------------------
2.00 3.00
(1 row(s) affected)
------解决方案--------------------
create table t_1
(x numeric(18,2),
y numeric(18,2)
)
insert into t_1 values(1,2)
insert into t_1 values(2,3)
insert into t_1 values(3,5)
insert into t_1 values(1,5)
insert into t_1 values(3,2)
declare @x numeric(18,2),@y numeric(18,2)
select @x = 4,@y = 5
select top 1 x,y from (select x,y,jl = SQRT(SQUARE([email protected]) +SQUARE([email protected])) from t_1)A order by A.jl
drop table t_1