需求:
数据库中 一个位置点address 含有2个字段 lon (经度-double) lat (纬度-double)我要传入一个指定点pointAddress myLon(经度-double) myLat(纬度-double)查询是需要根据我的指定点 到一位置点之间的距离进行排序:SQL语句该怎么写啊? select * from table where order by ( [color=#0000FF]sqrt( pow(abs(lon-mylon)) + pow(abs(lat-myLat)) )[/color] ) descsqrt:正平方根, pow :平方 abs:绝对值 2.像这样的排序是应该放在sql里面还是在我的编程语言(java)中实现比较好(效率更高)?
数据库中 一个位置点address 含有2个字段 lon (经度-double) lat (纬度-double)我要传入一个指定点pointAddress myLon(经度-double) myLat(纬度-double)查询是需要根据我的指定点 到一位置点之间的距离进行排序:SQL语句该怎么写啊? select * from table where order by ( [color=#0000FF]sqrt( pow(abs(lon-mylon)) + pow(abs(lat-myLat)) )[/color] ) descsqrt:正平方根, pow :平方 abs:绝对值 2.像这样的排序是应该放在sql里面还是在我的编程语言(java)中实现比较好(效率更高)?
一般来讲,尽量用SQL语句解决,当然要根据具体情况来判断。
lon lat
101.735549926758 25.498735427856
101.804817199707 26.151327133179
101.830314636230 26.199480056763…………我传入的
mylon ,mylat
101.9 27.0
根据点到点的距离的公式算出排序后的结果
101.830314636230 26.199480056763
101.804817199707 26.151327133179
101.735549926758 25.498735427856也就是根据 sqrt( pow(abs(lon-mylon)) + pow(abs(lat-myLat)) ) as distace 根据distance来排序
select *,sqrt( pow(abs(lon-mylon)) + pow(abs(lat-myLat)) ) as distace
from tt order by distace
from address
order by POW(lon-101.9,2)+POW(lat-27.0,2)
如果你不需要计算距离,只是排序的话,则只需要依据 (x1-x2)^2 + (y1-Y2)^2 排就可以,无需再计算平方根。select *
from address
order by (lon-101.9)*(lon-101.9)+(lat-27.0)*(lat-27.0);
from sichuan_2_26 order by distace' at line 1
本来我的数据查询都是使用 索引 进行查询的。。
是这样 select * from table whre id in(idArrays....) and order by ( (lon-101.9)*(lon-101.9)+(lat-27.0)*(lat-27.0) )
如果在数据库添加这个距离的查询,是不是要遍历表啊??还是先选出 id 再在这些id中判断距离?
先查ID,再判断距离
order by ( (lon-101.9)*(lon-101.9)+(lat-27.0)*(lat-27.0) )
是一条还是每一条记录后面都要有count(*)
一条的话 是不是这样?select count(*) from tt where ......
还是 在找出的结果
第一个记录是count(*) 的记录数?
每一条记录要用到分组
SELECT F1,F2... FROM ...
LEFT JOIN .... ON ....
GROUP BY F1,F2....基本这种语法
不是很懂。。
请问一下能不能在 结果的第一行 显示 count(*)
怎么添加啊?
上面语句是可以执行的!!
table1:
id name
1 fank
table1:
id name
1 test1
2 asd2
3 test3
4 asd4
5 test5
结果类似:id name counts
1 test1 3
3 test3 3
5 test5 3
select count(*) from sichuan_2_26 where gridID in(5518184078,5518184079,5518184080,5518364078,5518364079,5518364080,5518544078,5518544079,5518544080) and kind like '220%' order by ((longitude-104.079429626465)*(longitude-104.079429626465)+(latitude-30.657341003418)*(latitude-30.657341003418)) asc
select * from sichuan_2_26,
(select count(*) from sichuan_2_26 where gridID in(5518184078,5518184079,5518184080,5518364078,5518364079,5518364080,5518544078,5518544079,5518544080) and kind like '220%' order by ((longitude-104.079429626465)*(longitude-104.079429626465)+(latitude-30.657341003418)*(latitude-30.657341003418)) asc
)
where gridID in(5518184078,5518184079,5518184080,5518364078,5518364079,5518364080,5518544078,5518544079,5518544080) and kind like '220%' order by ((longitude-104.079429626465)*(longitude-104.079429626465)+(latitude-30.657341003418)*(latitude-30.657341003418)) asc
(select count(*) from sichuan_2_26 where gridID in(5518184078,5518184079,5518184080,5518364078,5518364079,5518364080,5518544078,5518544079,5518544080) and kind like '220%' order by ((longitude-104.079429626465)*(longitude-104.079429626465)+(latitude-30.657341003418)*(latitude-30.657341003418)) asc
) as counts
where gridID in(5518184078,5518184079,5518184080,5518364078,5518364079,5518364080,5518544078,5518544079,5518544080) and kind like '220%' order by ((longitude-104.079429626465)*(longitude-104.079429626465)+(latitude-30.657341003418)*(latitude-30.657341003418)) asc