当前位置: 动力学知识库 > 问答 > 编程问答 >

php - My MySQL SELECT Query is Returning Multiples of the Same Rows

问题描述:

For some reason I'm getting just one duplicate row that I can see, but can't really pinpoint where in the query I've gone wrong.

$stmt = $mysqli->query("

SELECT DISTINCT vendors_tbl.email AS email,

(vendor_avails_tbl.standard_pricing - vendor_loc_tbl.offpeak_time_pricing) AS best_margins,

vendor_loc_tbl.location_id AS locationID,

vendor_loc_tbl.loc_img_path AS locImg,

vendor_loc_tbl.offpeak_time_pricing AS offpeak,

vendor_loc_tbl.address1 AS address1,

vendor_loc_tbl.address2 AS address2,

vendor_loc_tbl.zip_code AS zip,

vendor_loc_tbl.geocodes AS geo,

vendor_loc_tbl.has_valet AS valet,

vendor_loc_tbl.has_transport AS transport,

vendor_loc_tbl.has_wheelchair AS wheelchair,

vendor_loc_tbl.has_desk AS desk,

vendor_loc_tbl.has_24hours AS open24hrs,

vendor_loc_tbl.has_covered AS covered,

vendor_loc_tbl.has_security AS security,

(vendor_avails_tbl.available_economy + vendor_avails_tbl.available_standard + vendor_avails_tbl.available_midsize + vendor_avails_tbl.available_truck_suv) AS avail_total,

vendor_avails_tbl.standard_pricing AS standard_pricing, 69 *

DEGREES(ACOS(COS(RADIANS($e_lat))

* COS(RADIANS(SUBSTR(vendor_loc_tbl.geocodes, 1, 10)))

* COS(RADIANS($e_lon) - RADIANS(SUBSTR(vendor_loc_tbl.geocodes, 13)))

+ SIN(RADIANS($e_lat))

* SIN(RADIANS(SUBSTR(vendor_loc_tbl.geocodes, 1, 10))))) AS distance_in_m

FROM vendors_tbl

INNER JOIN vendor_loc_tbl ON vendor_loc_tbl.vendor_id = vendors_tbl.vendor_id

INNER JOIN vendor_avails_tbl ON vendor_avails_tbl.location_id = vendor_loc_tbl.location_id

WHERE vendor_avails_tbl.available_standard > 0

ORDER BY vendor_loc_tbl.override_level DESC, best_margins DESC, distance_in_m ASC

LIMIT 5

");

网友答案:

Your distinct create the group of all the fields you have specified as you did not specify the group by condition. So there is no chance to get duplicate value.

网友答案:

Well In some cases it's bettor to use GROUP BY statement than DISTINCT. In your example you should add a group by at least on one col (some unique for example id). When you're using aggregates in query it is better to use group by, when using only joins - distinct is enough.

分享给朋友:
您可能感兴趣的文章:
随机阅读: