1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
|
SELECT
carbeo_station.id AS idStation,
carbeo_station.nom AS nomStation,
carbeo_station.enseigne AS enseigneStation,
carbeo_station.adr1 AS adr1Station,
carbeo_station.adr2 AS adr2Station,
carbeo_station.coord_multimap,
carbeo_station.ville AS villeStation,
CASE WHEN carbeo_station.coord_multimap = 1 THEN 0 ELSE carbeo_station.latitude END as stationLat,
CASE WHEN carbeo_station.coord_multimap = 1 THEN 0 ELSE carbeo_station.longitude END as stationLong,
carbeo_station.cat AS catStation,
carbeo_station.dernier_prix AS lastPrice,
cp.user AS lastTimePoster,
carbeo_enseigne.id AS idEnseigne,
carbeo_enseigne.nom AS nomEnseigne,
carbeo_enseigne.path AS pathEnseigne,
carbeo_enseigne.path_bg AS pathFondEnseigne,
carbeo_enseigne.aff_logo AS affEnseigne,
carbeo_ville.id AS idVille,
cv.nom AS nomVille,
carbeo_ville.arrond AS arrondVille,
cv.cp AS cpVille,
carbeo_ville.latitude AS villeLat,
carbeo_ville.longitude AS villeLong,
SQRT(POWER(111.317076533 * ABS(carbeo_ville.latitude - carbeo_station.latitude), 2) + POWER(111.317076533 * ABS(carbeo_ville.longitude - carbeo_station.longitude), 2)) AS dist, timeSP98, timeSP95, timeGas, timeGO, timeGPL, pxGas, pxSP95, pxSP98, pxGO, pxGPL, pxE85, CASE WHEN carbeo_station.dernier_prix = -1 THEN -1 ELSE cp.creation_time END as lastTime
, posterSP95, posterSP98, posterGas, posterGO, posterGPL, posterE85
, idSP98, idSP95, idGas, idGO, idGPL, idE85
FROM
carbeo_ville,
carbeo_ville v2,
carbeo_ville cv,
carbeo_prix cp,
carbeo_enseigne,
carbeo_station,
carbeo_station ss
left join (select creation_time as timeGas, prix as pxGas, station, user AS posterGas, id AS idGas From carbeo_prix Where carburant = '1' AND aff = 1) px1 on (px1.station = ss.id)
left join (select creation_time as timeSP95, prix as pxSP95, station, user AS posterSP95, id AS idSP95 From carbeo_prix Where carburant = '2' AND aff = 1) px2 on (px2.station = ss.id)
left join (select creation_time as timeSP98, prix as pxSP98, station, user AS posterSP98, id as idSP98 From carbeo_prix Where carburant = '3' AND aff = 1) px3 on (px3.station = ss.id)
left join (select creation_time as timeGO, prix as pxGO, station, user AS posterGO, id as idGO From carbeo_prix Where carburant = '4' AND aff = 1) px4 on (px4.station = ss.id)
left join (select creation_time as timeGPL, prix as pxGPL, station, user AS posterGPL, id as idGPL From carbeo_prix Where carburant = '5' AND aff = 1) px5 on (px5.station = ss.id)
left join (select creation_time as timeE85, prix as pxE85, station, user AS posterE85, id as idE85 from carbeo_prix where carburant = '7' AND aff = 1) px7 on (px7.station = ss.id)
WHERE
carbeo_ville.id = '22222'
AND
SQRT(POWER(111.317076533 * ABS(carbeo_ville.latitude - carbeo_station.latitude), 2) + POWER(111.317076533 * ABS(carbeo_ville.longitude - carbeo_station.longitude), 2)) < 10
AND
v2.id = carbeo_station.ville
AND
carbeo_enseigne.id = carbeo_station.enseigne
AND
carbeo_station.id = ss.id
AND
carbeo_station.dernier_prix = cp.id
AND
cv.id = carbeo_station.ville
AND carbeo_station.only_fioul = 0 ORDER BY lastTime DESC LIMIT 0, 10 |
Partager