34,594
社区成员
发帖
与我相关
我的任务
分享
drop table evdorate
create table tb1(name varchar(10),lng numeric(18,6),dmn numeric(18,6))
insert into tb1 select '桂东沙田', 113.807, 25.83 union
select '桂东广电', 110.933, 26.0564
create table tb2(name varchar(10),lng numeric(18,6),dmn numeric(18,6))
insert into tb2
select '径口',113.80257,25.822257 union
select '横屋',113.803948,25.820615 union
select '云岭',113.80613,25.821791 union
select '石桥镇',112.402757,25.736888 union
select '肖家镇',112.475639,25.69247 union
select '普满乡',112.516254,25.694456
select * from tb1 a,tb2 b where
b.name in(select top 4 name from tb2 order by
100*ROUND(SQRT(POWER((a.lng-lng),2)+POWER((a.dmn-dmn),2)),6) desc)
/*
name lng dmn name lng dmn
---------- --------------------------------------- --------------------------------------- ---------- --------------------------------------- ---------------------------------------
桂东沙田 113.807000 25.830000 横屋 113.803948 25.820615
桂东沙田 113.807000 25.830000 普满乡 112.516254 25.694456
桂东沙田 113.807000 25.830000 石桥镇 112.402757 25.736888
桂东沙田 113.807000 25.830000 肖家镇 112.475639 25.692470
桂东广电 110.933000 26.056400 横屋 113.803948 25.820615
桂东广电 110.933000 26.056400 径口 113.802570 25.822257
桂东广电 110.933000 26.056400 普满乡 112.516254 25.694456
桂东广电 110.933000 26.056400 云岭 113.806130 25.821791
(8 行受影响)
(select top 10 c.name from b order ROUND(SQRT(POWER((a.lng-b.lng),2)+POWER((a.dmn-b.dmn),2)),6))
select *, 100*ROUND(SQRT(POWER((a.lng-b.lng),2)+POWER((a.dmn-b.dmn),2)),6) distance from a,b
where b.name
in(select top 10 c.name from b order ROUND(SQRT(POWER((a.lng-b.lng),2)+POWER((a.dmn-b.dmn),2)),6))
select top *, 100*ROUND(SQRT(POWER((a.lng-b.lng),2)+POWER((a.dmn-b.dmn),2)),6) distance from a,b where b.name in(select top 10 c.name from b order ROUND(SQRT(POWER((a.lng-b.lng),2)+POWER((a.dmn-b.dmn),2)),6))
--100*ROUND(SQRT(POWER((a.lng-b.lng),2)+POWER((a.dmn-b.dmn),2)),6)这个是不是算距离的?
select * from 表A a,表B b where
b.name in(select top 10 name from 表B order by
100*ROUND(SQRT(POWER((a.lng-b.lng),2)+POWER((a.dmn-b.dmn),2)),6) desc)