mysql - SQL - 即使使用 GROUP BY 查询也会返回重复记录

Question

所以我有这个查询:

SELECT P.*,
     ( 3959 * acos( cos( radians('37.785834') ) 
     * cos( radians( CA.lat ) ) 
     * cos( radians( CA.lng ) 
     - radians('-122.406417') ) 
     + sin( radians('37.785834') ) 
     * sin( radians( CA.lat ) ) ) ) AS distance 
FROM adp2_posts as P 
JOIN adp2_offer_details C ON C.merchant_id = P.ID 
JOIN adp2_addresses CA ON CA.address_id = C.address_id 
WHERE P.post_type = 'merchant' 
    AND P.post_status = 'publish' 
    AND CA.mm = "dallasftworth" 
    AND C.cats RLIKE CONCAT("[[:<:]]", REPLACE(199, ",", "[[:>:]]|[[:<:]]"), "[[:>:]]") 
GROUP BY CA.address_id, P.ID 
HAVING (distance < 999999999999999999 AND P.coupon_count > 0) ORDER BY distance ASC LIMIT 0 , 20

我希望它从左表返回唯一值，但它也返回重复值。

结果:

我对这个有点困惑。

Answer 1

抱歉没有提供完整的上下文，这是漫长的一天，但我终于弄清楚了。

这是最终查询:

SELECT P.*, MIN(( 3959 * acos( cos( radians('37.785834') ) * cos( radians( CA.lat ) ) * cos( radians( CA.lng ) - radians('-122.406417') ) + sin( radians('37.785834') ) * sin( radians( CA.lat ) ) ) )) AS distance FROM adp2_posts as P JOIN adp2_offer_details C ON C.merchant_id = P.ID JOIN adp2_addresses CA ON CA.address_id = C.address_id WHERE P.post_type = 'merchant' AND P.post_status = 'publish' AND CA.mm = "dallasftworth" AND C.cats RLIKE CONCAT("[[:<:]]", REPLACE(199, ",", "[[:>:]]|[[:<:]]"), "[[:>:]]") GROUP BY P.ID HAVING (distance < 999999999999999999 AND P.coupon_count > 0) ORDER BY distance ASC LIMIT 0 , 20

问题是，CA.address_id where different 和 GROUP BY 将其视为唯一值。我所做的只是选择距离最小的 P.* (这正是我的业务逻辑所要求的)。