mysql - 如何使用 mysql 在评级系统中获得最高平均值？

Question

我正在我的产品页面中创建一个排序，并且我使用评级实现了一个排序。我的问题是我得到了多行相同的产品。

这是我的查询:

SELECT DISTINCT(p.product_id) AS product_id, p.model AS model, r.rating AS rating FROM jp_product AS p
LEFT JOIN jp_review AS r
ON(p.product_id = r.product_id)
LEFT JOIN jp_user AS u
ON(p.seller_id = u.user_id)
WHERE p.`status` = 1
AND u.`status` = 1
ORDER BY r.rating DESC;

结果是这样的:

*************************** 1. row ***************************
product_id: 143
     model: Kagoshima Satsuma-age
    rating: 5
*************************** 2. row ***************************
product_id: 145
     model: Filter-in Bottle
    rating: 5
*************************** 3. row ***************************
product_id: 143
     model: Kagoshima Satsuma-age
    rating: 2
*************************** 4. row ***************************
product_id: 145
     model: Filter-in Bottle
    rating: 2
*************************** 5. row ***************************
product_id: 51
     model: 4901362107276
    rating: NULL
*************************** 6. row ***************************
product_id: 69
     model: a3
    rating: NULL

....

如您所见，产品 ID 143 和 145 是重复的。你能帮我解决这个问题吗？

Answer 1

使用 MAX(rating)。

SELECT p.product_id AS product_id, p.model AS model, MAX(r.rating) AS      
rating
FROM jp_product AS p
LEFT JOIN jp_review AS r
    ON (p.product_id = r.product_id)
LEFT JOIN jp_user AS u
    ON (p.seller_id = u.user_id)
WHERE p.`status` = 1 AND u.`status` = 1
GROUP BY p.product_id, p.model
ORDER BY r.rating DESC;