在三个表上使用count()进行内部联接 [英] Inner join with count() on three tables
问题描述
简单快速的问题,我有那些表:
Simple and fast question, i have those tables:
//table people
| pe_id | pe_name |
| 1 | Foo |
| 2 | Bar |
//orders table
| ord_id | pe_id | ord_title |
| 1 | 1 | First order |
| 2 | 2 | Order two |
| 3 | 2 | Third order |
//items table
| item_id | ord_id | pe_id | title |
| 1 | 1 | 1 | Apple |
| 2 | 1 | 1 | Pear |
| 3 | 2 | 2 | Apple |
| 4 | 3 | 2 | Orange |
| 5 | 3 | 2 | Coke |
| 6 | 3 | 2 | Cake |
我需要一个查询列出所有人员,计算订单数和总数项数,如下所示:
I need to have a query listing all the people, counting the number of orders and the total number of items, like that:
| pe_name | num_orders | num_items |
| Foo | 1 | 2 |
| Bar | 2 | 4 |
但是我无法使其正常工作! 我尝试过
But i can not make it work! I tried
SELECT
people.pe_name,
COUNT(orders.ord_id) AS num_orders,
COUNT(items.item_id) AS num_items
FROM
people
INNER JOIN orders ON (orders.pe_id = people.pe_id)
INNER JOIN items ON items.pe_id = people.pe_id
GROUP BY
people.pe_id;
但是这会返回错误的num_*
值:
But this returns the num_*
values incorrect:
| name | num_orders | num_items |
| Foo | 2 | 2 |
| Bar | 8 | 8 |
我注意到,如果我尝试一次加入一张桌子,它将起作用:
I noticed that if i try to join one table at time, it works:
SELECT
people.pe_name,
COUNT(orders.ord_id) AS num_orders
FROM
people
INNER JOIN orders ON (orders.pe_id = people.pe_id)
GROUP BY
people.pe_id;
//give me:
| pe_name | num_orders |
| Foo | 1 |
| Bar | 2 |
//and:
SELECT
people.pe_name,
COUNT(items.item_id) AS num_items
FROM
people
INNER JOIN items ON (items.pe_id = people.pe_id)
GROUP BY
people.pe_id;
//output:
| pe_name | num_items |
| Foo | 2 |
| Bar | 4 |
如何将这两个查询合而为一?
How to combine those two queries in one?
推荐答案
将商品与订单一起加入比与人一起来更有意义!
It makes more sense to join the item with the orders than with the people !
SELECT
people.pe_name,
COUNT(distinct orders.ord_id) AS num_orders,
COUNT(items.item_id) AS num_items
FROM
people
INNER JOIN orders ON orders.pe_id = people.pe_id
INNER JOIN items ON items.ord_id = orders.ord_id
GROUP BY
people.pe_id;
与人们一起加入物品会招来很多麻烦. 例如,订单3中的蛋糕将通过人与人之间的联接而与订单2链接,而您不希望这种情况发生!
Joining the items with the people provokes a lot of doublons. For example, the cake items in order 3 will be linked with the order 2 via the join between the people, and you don't want this to happen !!
所以:
1-您需要对架构有一个很好的了解.物料链接到订单,而不是人员.
1- You need a good understanding of your schema. Items are link to orders, and not to people.
2-您需要为一个人计算不同的订单,否则您将计算与订单一样多的商品.
2- You need to count distinct orders for one person, else you will count as many items as orders.
这篇关于在三个表上使用count()进行内部联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!