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)
离(3,3)最小: select * from [Table] where square(a-3)+square(b-3)=(select min(square(a-3)+square(b-3)) from [Table])
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 = 2select top 1 *,power((x-@x)*(x-@x)+(y-@y)*(y-@y),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)
(1,2)和(2,1)到(2,2)的距离是一样的,top 1只能得到一条记录
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 = 5select top 1 x,y from (select x,y,jl = SQRT(SQUARE(x-@x) +SQUARE(y-@y)) from t_1)A order by A.jl drop table t_1
再改下把所有相等距離的記錄找出來 select *,(sqrt((x-3)*(x-3)+(y-3)*(y-3)))as 距離 from t_1 where (sqrt((x-3)*(x-3)+(y-3)*(y-3)))in ( select (sqrt((x-3)*(x-3)+(y-3)*(y-3))) from t_1 group by (sqrt((x-3)*(x-3)+(y-3)*(y-3))) having count(*)>1 )x y 距離 -------------------- -------------------- ----------------------------------------------------- 2.00 3.00 1.0 3.00 2.00 1.0(2 row(s) affected)
不好意思,上面這條語句不行select *,(sqrt((x-3)*(x-3)+(y-3)*(y-3)))as 距離 from t_1 where sqrt((x-3)*(x-3)+(y-3)*(y-3))=(select top 1 sqrt((x-3)*(x-3)+(y-3)*(y-3)) from t_1 order by sqrt((x-3)*(x-3)+(y-3)*(y-3)) )x y 距離 -------------------- -------------------- ----------------------------------------------------- 2.00 3.00 1.0 3.00 2.00 1.0(2 row(s) affected) 這樣就可以了
(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)
select * from [Table] where square(a-3)+square(b-3)=(select min(square(a-3)+square(b-3)) from [Table])
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 = 2select top 1 *,power((x-@x)*(x-@x)+(y-@y)*(y-@y),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)
(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 = 5select top 1 x,y from (select x,y,jl = SQRT(SQUARE(x-@x) +SQUARE(y-@y)) from t_1)A order by A.jl
drop table t_1
select *,(sqrt((x-3)*(x-3)+(y-3)*(y-3)))as 距離 from t_1
where (sqrt((x-3)*(x-3)+(y-3)*(y-3)))in
(
select (sqrt((x-3)*(x-3)+(y-3)*(y-3))) from t_1
group by (sqrt((x-3)*(x-3)+(y-3)*(y-3)))
having count(*)>1
)x y 距離
-------------------- -------------------- -----------------------------------------------------
2.00 3.00 1.0
3.00 2.00 1.0(2 row(s) affected)
where sqrt((x-3)*(x-3)+(y-3)*(y-3))=(select top 1 sqrt((x-3)*(x-3)+(y-3)*(y-3)) from t_1 order by sqrt((x-3)*(x-3)+(y-3)*(y-3)) )x y 距離
-------------------- -------------------- -----------------------------------------------------
2.00 3.00 1.0
3.00 2.00 1.0(2 row(s) affected)
這樣就可以了