SQL 在三个表上使用 count() 进行内连接

Inner join with count() on three tables

提问by Strae

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 totalnumber 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;

But this returns the num_*values incorrect:

但这会返回num_*不正确的值：

| 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?

如何将这两个查询合二为一？

回答by Cyril Gandon

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;

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 !!

与人们一起加入这些物品会引起大量的金币。例如，订单 3 中的蛋糕项目将通过人与人之间的连接与订单 2 相关联，您不希望发生这种情况！！

So :

所以 ：

1- You need a good understanding of your schema. Items are link to orders, and not to people.

1- 您需要很好地了解您的架构。项目链接到订单，而不是人。

2- You need to count distinct orders for one person, else you will count as many items as orders.

2-您需要为一个人计算不同的订单，否则您将计算与订单一样多的项目。

回答by Tom H

As Frank pointed out, you need to use DISTINCT. Also, since you are using composite primary keys (which is perfectly fine, BTW) you need to make sure that you use the whole key in your joins:

正如弗兰克指出的那样，您需要使用 DISTINCT。此外，由于您使用的是复合主键（这很好，顺便说一句），您需要确保在连接中使用整个键：

SELECT
    P.pe_name,
    COUNT(DISTINCT O.ord_id) AS num_orders,
    COUNT(I.item_id) AS num_items
FROM
    People P
INNER JOIN Orders O ON
    O.pe_id = P.pe_id
INNER JOIN Items I ON
    I.ord_id = O.ord_id AND
    I.pe_id = O.pe_id
GROUP BY
    P.pe_name

Without I.ord_id = O.ord_id it was joining each item row to every order row for a person.

如果没有 I.ord_id = O.ord_id，它会将每个项目行连接到一个人的每个订单行。

回答by Marjunne Romero

i tried putting distinct on both, count(distinct ord.ord_id) as num_order, count(distinct items.item_id) as num items

我尝试将不同的放在两者上，count(distinct ord.ord_id) 作为 num_order，count(distinct items.item_id) 作为 num items

its working :)

它的工作:)

    SELECT
         people.pe_name,
         COUNT(distinct orders.ord_id) AS num_orders,
         COUNT(distinct 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;

Thanks for the Thread it helps :)

感谢您的主题，它有帮助:)

回答by Kamal Deepak

select pe_name,count( distinct b.ord_id),count(c.item_id) 
 from people  a, order1 as b ,item as c
 where a.pe_id=b.pe_id and
b.ord_id=c.order_id   group by a.pe_id,pe_name

回答by Frank

Your solution is nearly correct. You could add DISTINCT:

您的解决方案几乎是正确的。您可以添加 DISTINCT：

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.pe_id = people.pe_id
GROUP BY
    people.pe_id;

回答by Alamgir Mand

One needs to understand what a JOIN or a series of JOINs does to a set of data. With strae's post, a pe_id of 1 joined with corresponding order and items on pe_id = 1 will give you the following data to "select" from:

需要了解 JOIN 或一系列 JOIN 对一组数据的作用。在 strae 的帖子中，pe_id 为 1 并与 pe_id = 1 上的相应订单和项目相结合，将为您提供以下数据以供“选择”：

[ table people portion ] [ table orders portion ] [ table items portion ]

| people.pe_id | people.pe_name | orders.ord_id | orders.pe_id | orders.ord_title | item.item_id | item.ord_id | item.pe_id | item.title |

| 1 | Foo | 1 | 1 | First order | 1 | 1 | 1 | Apple |
| 1 | Foo | 1 | 1 | First order | 2 | 1 | 1 | Pear |

[餐桌人员部分] [餐桌订单部分] [餐桌物品部分]

| people.pe_id | people.pe_name | 订单.ord_id | 订单.pe_id | 订单.ord_title | item.item_id | item.ord_id | item.pe_id | item.title |

| 1 | 福 | 1 | 1 | 第一单 | 1 | 1 | 1 | 苹果 |
| 1 | 福 | 1 | 1 | 第一单 | 2 | 1 | 1 | 梨 |

The joins essentially come up with a cartesian product of all the tables. You basically have that data set to select from and that's why you need a distinct count on orders.ord_id and items.item_id. Otherwise both counts will result in 2 - because you effectively have 2 rows to select from.

联接基本上提出了所有表的笛卡尔积。您基本上拥有可供选择的数据集，这就是为什么您需要对 orders.ord_id 和 items.item_id 进行不同计数的原因。否则，两个计数都将导致 2 - 因为您实际上有 2 行可供选择。