- 积分
- 46
- UID
- 20016
- 阅读权限
- 10
- 注册时间
- 2015-10-12
- 精华
- 在线时间
- 小时
- 最后登录
- 1970-1-1
- 职业
- 1
|
一条关于用户距离按远近程度排序的SQL如何优化
要求是知道每个用户的经纬度,然后给登录用户分页推荐用户,按用户距离进行排序 最近的用户优先
SELECT a.seqID,a.userID,a.supportNum,a.longitude,a.latitude,b.sex,b.imageURL,b.approved headapproved, round(6378.138*2*asin(sqrt(pow(sin( (a.latitude*pi()/180-39.984785*pi()/180)/2),2)+ cos(a.latitude*pi()/180)*cos(39.984785*pi()/180)* pow(sin( (a.longitude*pi()/180- 116.32244*pi()/180)/2),2)))*1000)/1000 distance, case when constellation in ("巨蟹座","天蝎座","双鱼座") then "1" when constellation in ("白羊座","狮子座","射手座") then "2" when constellation in ("金牛座","处女座","摩羯座") then "3" when constellation in ("双子座","天秤座","水瓶座") then "4" end as galaxy FROM t_user_profile a, user b WHERE 1=1 and a.userID=b.userID and a.userID!=350369 and a.birthday between '1915-10-12 00:00:00' and '1997-10-12 00:00:00' ORDER BY b.approved desc,distance asc,galaxy asc LIMIT 0,12
|
|