mysql - 计数在连接的 mysql 查询中返回错误值

Question

我无法获得与产品相关的评论的正确计数(查询的最后一个变量)。它正在返回远离公园的数字。除非没有评论，否则它会正确回显 0。

$queryProduct = $mysqli->prepare("
    SELECT p.product_id, p.product_name, p.product_pic, AVG(r.review_stars), MIN(c.price_price), p.product_url, v.vendor_name, p.product_clicks, SUM(c.price_clicks), COUNT(c.price_price), c.price_affiliate, COUNT(r.review_id)
    FROM products as p 
    LEFT OUTER JOIN reviews as r ON p.product_id = r.review_product
    LEFT OUTER JOIN prices as c ON c.price_product = p.product_id
    LEFT OUTER JOIN vendors as v ON c.price_vendor = v.vendor_id
    GROUP BY p.product_id
    ORDER BY p.product_clicks DESC
    LIMIT 21");
$queryProduct->execute();
$queryProduct->bind_result($product_id, $product_name, $product_pic, $review_stars, $price_price, $product_url, $vendor_name, $product_clicks, $price_clicks, $num_vendors, $price_affiliate, $num_reviews);
while($queryProduct->fetch()) {
    echo $num_reviews;
}
$queryProduct->close();

Answer 1

我大胆的猜测是您需要 COUNT(DISTINCT r.review_id)。

要调试此查询，您首先要检查具有错误值的单个 product_id，然后删除所有聚合以检查问题出在哪里。例如，我将从运行开始

SELECT p.product_id, p.product_name, p.product_pic, r.review_stars, c.price_price, p.product_url, v.vendor_name, p.product_clicks, c.price_clicks, c.price_price, c.price_affiliate, r.review_id
FROM products as p 
LEFT OUTER JOIN reviews as r ON p.product_id = r.review_product
LEFT OUTER JOIN prices as c ON c.price_product = p.product_id
LEFT OUTER JOIN vendors as v ON c.price_vendor = v.vendor_id
WHERE p.product_id = 12345 -- choose a bad id in your output 
ORDER BY p.product_clicks DESC