当前位置: 代码迷 >> Sql Server >> select语句,
  详细解决方案

select语句,

热度:32   发布时间:2016-04-27 20:19:13.0
求一个select语句,急,急,急!!
我的数据表里有点的平面坐标(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
  相关解决方案