There is no correlation between these two data, and the collation in the SQL statement does not meet the requirements: SQL only the data in the previous sorting criteria in the same situation to consider the following sorting criteria. The reality is that if you sort the distance by the first push time, the distance does not work, and vice versa.
For two data to be associated, one approach is to sort the two data by addition or subtraction, but this must take into account the following
Data type of two data is inconsistent, one is date type and the other is double precision type
Must unify two data the sort direction, cannot push the time to take the positive sequence and the distance takes the reverse order, depends on the actual demand
Converting both of these data into one type requires a factor to balance them. If one data is large in magnitude and the other is very small, then how the minimum data changes to the enormous data can also be ignored
The following is implemented by first converting the date type to a double-precision type, which is relatively easy to manipulate with two numeric data types. There's just one function in MySQL: Unix_timestamp (date)--Returns the internal timestamp value directly
Copy Code code as follows:
--The time stamp value of the day difference
Select Unix_timestamp (' 2010-12-13 ')-unix_timestamp (' 2010-12-12 ')
-Calculation results: 86400
On the known two-dimensional calculation of the distance between the formula, a search on the Internet, the calculation results are satisfactory
Copy Code code as follows:
ROUND ((2 * ASIN (SQRT) ((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)
Next, consider the sort direction of the two data, and we hope that the longer the last push time, the better the closer you are to the user, the better. The longer the calculation is, the larger the difference is, and the two data can be added directly. Here we have to take a negative time to meet the distance between the user and the better requirements, And then arrange them in positive order.
Again to let these two numbers in the same order of magnitude, according to the data test, time takes is seconds, distance calculated is kilometer, directly to the distance by the previous coefficient (1000) converted into meters to balance the two.
The final push statement is as follows:
Copy Code code as follows:
Select Bottleid, (ROUND (2 * ASIN (SQRT ((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) as/10000 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
--ROUND ((2 * ASIN (SQRT (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
ORDER BY
(-(Unix_timestamp (now ())-unix_timestamp (bottle.placetime)) *0.5 +
(ROUND (2 * ASIN (SQRT ((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;
Front-end screenshot:
