mysql join 3表但"COUNT“重复

Question

我有三张这样的桌子

订单表：

| id | product_id | status | created_at |
|----| ---------- | ------ | ---------- |
| 1  |     1      |  done  | 1607431726 |
| 2  |     7      |  done  | 1607431726 |
| 3  |     8      |  done  | 1607431726 |

产品表：

| id | user_id |    title    |  description  | created_at |
|----| ------- | ----------- | ------------- | ---------- |
| 1  |    1    |  product 1  | description 1 | 1607431726 |
| 7  |    3    |  product 2  | description 1 | 1607431726 |
| 8  |    3    |  product 3  | description 1 | 1607431726 |

评级表：

| id | client_id | content_type | content_id | rating | created_at |
|----| --------- | ------------ | ---------- | ------ | ---------- |
| 1  |     5     |     user     |      1     |    5   | 1607431726 |
| 2  |     4     |     user     |      3     |    5   | 1607431726 |
| 3  |     5     |     user     |      3     |    4   | 1607431726 |

从上面的3个表中，我想得到1个结果，其中有一个字段average_rating/user，total order/user，我想按average_rating & total_rating DESC排序。大致是这样的结果：

| user_id | average_rating | total_order |
| ------- | -------------- | ----------- |
|    1    |      5.0       |     1       |
|    3    |      4.5       |     2       |

这是我的问题：

SELECT b.user_id, round(avg(c.rating), 1) as total_rating, COUNT(a.id) as total_order
    FROM orders a
        LEFT JOIN products b ON a.product_id=b.id
        LEFT JOIN ratings c ON c.content_id=b.user_id 
    WHERE a.status = 'done' 
        AND c.content_type = 'user'
    GROUP BY b.user_id, c.content_id;

但是在我的查询中，user_id 1的总订单返回1，user_id 3的总订单返回4，结果是：

| user_id | average_rating | total_order |
| ------- | -------------- | ----------- |
|    1    |      5.0       |     1       |
|    3    |      4.5       |     4       |

我已经尝试过INNER JOIN，LEFT OUTER，RIGHT OUTER，RIGHT JOIN，但是结果是一样的。有谁能帮帮我吗？

Answer 1

每个产品都有多个评级。一种选择是使用distinct

SELECT p.user_id, 
    round(avg(r.rating), 1) as total_rating, 
    COUNT(distinct o.id) as total_order
FROM orders o
INNER JOIN products p ON o.product_id=p.id
INNER JOIN ratings r ON r.content_id=p.user_id 
WHERE o.status = 'done' AND r.content_type = 'user'
GROUP BY p.user_id, r.content_id;