在三个表上使用 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屋！