MySQL社区

 找回密码
 注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
查看: 2350|回复: 2

[思路] 一条关于用户距离按远近程度排序的SQL如何优化

[复制链接]
发表于 2015-10-12 14:45:50 | 显示全部楼层 |阅读模式
一条关于用户距离按远近程度排序的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





发表于 2015-10-13 18:12:14 | 显示全部楼层
{:soso__10169062262133571330_1:}
 楼主| 发表于 2015-10-14 11:44:22 | 显示全部楼层
没有人帮忙吗
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|申请友链|小黑屋|Archiver|手机版|MySQL社区 ( 京ICP备07012489号   
联系人:周生; 联系电话:13911732319

GMT+8, 2024-3-28 21:10 , Processed in 0.065406 second(s), 23 queries , Gzip On.

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表