本身這兩個數據之間沒有關聯,并且sql語句里面的排序規則不能滿足要求:sql里只有數據中前一個排序條件出現相同的情況時才考慮后面的排序條件.實際情況是如果按先推送時間后距離排序的話,距離就起不了作用,反之亦然.
要讓兩個數據產生關聯,有一種做法是將這兩個數據做加法或減法后排序,但是這必須要考慮以下情況
兩個數據的數據類型不一致,一個是日期類型另一個是雙精度類型
必須統一兩個數據的排序方向,不能推送時間取正序而距離取反序,視實際需求而定
將這兩個數據都轉換成一種類型,需要一個系數來平衡它們.如果一個數據數量級極大,另一個數據數量極小,那么極小的數據再怎么變化對極大的數據產生的影響也可以忽略
下面來具體實現,首先將日期類型轉換為雙精度類型,操作兩個數值類型的數據相對來說比較容易.mysql里面正好有這樣的函數:unix_timestamp(date) -- 直接返回內部時戳值
復制代碼 代碼如下:
-- 相差一天的時戳值
select unix_timestamp('2010-12-13') - unix_timestamp('2010-12-12')
-- 計算結果:86400
關于已知兩地經緯度計算距離的公式,在網上搜索了一個,計算結果還算滿意
復制代碼 代碼如下:
ROUND((2 * ASIN(SQRT(POWER(SIN(((LAT*PI()/180.0)-(plat*PI()/180.0)) / 2), 2) + COS(LAT*PI()/180.0) * COS(plat*PI()/180.0) * POWER(SIN((LNG*PI()/180.0-plng*PI()/180.0) / 2), 2))))*6378.137*10000,3)/10000)
接下來考慮兩個數據的排序方向,我們希望的是:距離上次推送時間越久越好,與用戶之間的距離越近越好.時間越久那么計算得出的差值就越大,兩個數據直接相加明顯不行.這里只好將時間取負值來滿足與用戶之間的距離越近越好的要求,然后再按正序排列.
再來讓這兩個數在同一數量級,根據數據測試,時間取的是秒,距離算出來是公里,直接把距離乘上一個系數(1000)換算成米來平衡二者.
最終的推送語句如下:
復制代碼 代碼如下:
select BottleID,(ROUND((2 * ASIN(SQRT(POWER(SIN(((LAT*PI()/180.0)-(plat*PI()/180.0)) / 2), 2) + COS(LAT*PI()/180.0) * COS(plat*PI()/180.0) * POWER(SIN((LNG*PI()/180.0-plng*PI()/180.0) / 2), 2))))*6378.137*10000,3)/10000)as Distance from Bottle
inner join Venue_Mapabc
on Bottle.VenueID = Venue_Mapabc.VenueID
where Bottle.IsDrift =1 and Bottle.IsEmpty =1 and IsFinished=0 and Venue_Mapabc.City = pCity
-- and (ROUND((2 * ASIN(SQRT(POWER(SIN(((LAT*PI()/180.0)-(plat*PI()/180.0)) / 2), 2) + COS(LAT*PI()/180.0) * COS(plat*PI()/180.0) * POWER(SIN((LNG*PI()/180.0-plng*PI()/180.0) / 2), 2))))*6378.137*10000,3)/10000)>1.5
order by
(-(unix_timestamp(now())-unix_timestamp(Bottle.PlaceTime))*0.5 +
(ROUND((2 * ASIN(SQRT(POWER(SIN(((LAT*PI()/180.0)-(plat*PI()/180.0)) / 2), 2) + COS(LAT*PI()/180.0) * COS(plat*PI()/180.0) * POWER(SIN((LNG*PI()/180.0-plng*PI()/180.0) / 2), 2))))*6378.137*10000,3)/10000)*0.5*1000)
limit 1;
前端截圖:
