两个左连接给了我不真实的数据(双数据?)与 MySQL [英] Two left joins gives me untrue data(double data?) with MySQL

问题描述

这是我的查询:

SELECT `products`.*, SUM(orders.total_count) AS revenue,
    SUM(orders.quantity) AS qty, ROUND(AVG(product_reviews.stars)) as avg_stars 
FROM `products` 
LEFT JOIN `orders`
    ON (`products`.`id` = `orders`.`product_id`) AND
    (`orders`.`status` = 'delivered' OR `orders`.`status` = 'new') 
LEFT JOIN product_reviews
    ON (products.id = product_reviews.product_id)
GROUP BY `products`.`ID`
ORDER BY products.ID DESC
LIMIT 10
OFFSET 0

当我进行第二个左连接时，我的第一个左连接数据、订单表中的收入和数量给了我根本不正确的值(太高了，很多双倍?)

When I have this second left join, my first left joins data, revenue and qty from orders table gives me values that are not true at all (way too high, many doubles?)

来自 这个问题.

我得到了一个半笛卡尔产品的指示，因此对产品的两次评论使数量翻了一番，我相信这是我的问题.

I got the direction that I am getting a semi-cartesian product, so two reviews for a product is doubling the quantities, and I believe this is my problem.

如何解决?

推荐答案

问题在于 product_reviews 和 orders 表中每个产品 ID 的行数可能超过一行.解决此问题的一种方法是使用子查询:

The problem is that the product_reviews and orders table can have more that one row per product id. One way you can fix this is to use a subquery:

SELECT `products`.*, 
  o.revenue,
  o.qty, 
  ROUND(avg_stars) as avg_stars 
FROM `products` 
LEFT JOIN
(
  select `product_id`, 
    sum(total_count) revenue,
    sum(quantity) qty
  from `orders`
  where `status` in ('delivered', 'new')
  group by `product_id`
) o
  ON `products`.`id` = o.`product_id`
LEFT JOIN
(
  select product_id, avg(stars) avg_stars
  from product_reviews
  group by product_id
) pr
    ON (products.id = pr.product_id)
ORDER BY products.ID DESC
LIMIT 10
OFFSET 0

这篇关于两个左连接给了我不真实的数据(双数据?)与 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！