mysql - 用三个表计数分组

Question

我的数据库架构如下所示:

User table

Shop table (foreign_key to User)

Item-Shop join table (foreign key to Item, foreign key to Shop)

Item table

因此，用户通过商店拥有许多商品。

我想找出每个用户拥有的商品数量，因为我知道一个用户可以有多个商店，每个商店可以有多个商品。

我需要为此执行什么 SQL 查询？所以，最后我希望是这样的:

User ID       Item count
 1                30
 2                50
 3                70

Answer 1

如果您想获得与用户相关的项目数量，您将必须加入不同的表并使用 GROUP BY子句:

SELECT u.user_id, COUNT(i.item_id) item_count FROM user u
INNER JOIN shop s ON s.user_id = u.user_id
INNER JOIN item_shop vis ON vis.shop_id = s.shop_id
INNER JOIN item i ON i.item_id = vis.item_id
GROUP BY u.user_id

如果您还想返回还没有任何元素的用户(item_count 将为 0)，您将需要使用 LEFT JOIN相反。

如果我们有以下示例数据:

+---------+ +---------+---------+ +---------+---------+ +---------+
| user_id | | shop_id | user_id | | item_id | shop_id | | item_id |
+---------+ +---------+---------+ +---------+---------+ +---------+
|       1 | |       1 |       1 | |       1 |       1 | |       1 |
|       2 | |       2 |       2 | |       2 |       2 | |       2 |
|       4 | |       2 |       1 | |       3 |       2 | |       3 |
+---------+ +---------+---------+ |       4 |       1 | |       4 |
                                  |       5 |       1 | |       5 |
                                  +---------+---------+ +---------+

你会得到如下结果:

+---------+------------+
| user_id | item_count |
+---------+------------+
|       1 |          5 |
|       2 |          2 |
+---------+------------+