有近到远分页显示附近的用户的所有信息。
将多条数据通过坐标计算出距离,按照距离排序以后再分页查询出由近到远的数据。
查询的sql语句:
SELECT
tt.id,
tt.user_id,
tt.title,
tt.description,
tt.location,
(
SELECT
getDistance (
#{latitude},#{longitude},IFNULL(tt.latitude,0),IFNULL(tt.longitude,0))) distance,tt.sexlimit,tt.isonline,tt.agelimit,tt.validity,tt.price,tt.satate,tt.js_time,tt.unit,tt.release_time,
tt.over_description,
tt.over_price,
tt.satate,
tu.sex,
tu.nickname,
tu.avatar,
tu.userlevel,
tu.is_verified,
tu.star_level,
tu.phone_number,
tu.occupation,
f.images_path,
tjs.reason,
(
SELECT
nickname
FROM
表1
WHERE
id = tjs.user_id
) by_nickName,
ru.ranklist card,
ru.ranklist_city cid,
ru.ranklist_province validity_number,
hu.ranklist card_no,
hu.ranklist_city image_name,
hu.ranklist_province del,
vip.vip_level hg_id,
IFNULL(vip.vip_type, 0) vip_type
FROM
t_task tt
LEFT JOIN 表1 tu ON tt.user_id = tu.id
LEFT JOIN 表2 f ON tt.id = f.task_id
LEFT JOIN 表3 ON tt.id = tjs.task_id
LEFT JOIN 表4 ru ON tt.user_id = ru.user_id
LEFT JOIN 表5 hu ON tt.user_id = hu.user_id
LEFT JOIN 表6 vip ON tt.user_id = vip.user_id
WHERE
(
tt.title LIKE #{title} or tt.id in (SELECT s.task_id FROM t_task_sort s WHERE s.hg_id in (SELECT h.id FROM t_hotcategories h WHERE h.hg_name LIKE #{hg})) or tu.nickname LIKE #{nick} )
AND (
(
tt.location LIKE #{hgnameUrl} AND tt.isonline = 1) OR tt.isonline = 0)
AND date_format(now(), '%Y-%m-%d %H:%i:%s') & lt;
date_format(
tt.validity,
'%Y-%m-%d %H:%i:%s'
)
AND tt.satate = 1
AND tt.del = 0
GROUP BY
tt.id
ORDER BY
distance ASC
LIMIT #{pageStart},#{pageSize}
上面调用通过坐标计算距离的函数的getDistance(latitude1,longitude1,latitude2,longitude2)函数:
BEGIN
DECLARE dis DOUBLE;
set dis = ACOS(SIN((curLat * 3.1415) / 180 ) * SIN((shopLat * 3.1415) / 180 ) + COS((curLat * 3.1415) / 180 ) * COS((shopLat * 3.1415) / 180 ) * COS((curLon * 3.1415) / 180 - (shopLon * 3.1415) / 180 ) ) * 6380*1000 ;
RETURN dis;
END