sql – 在三个表上连接到count()

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

我需要查询列出所有的人,计数订单数量和总数量,如下所示：

| pe_name | num_orders | num_items |
| Foo  |    1       |   2       |
| Bar  |    2       |   4       |

但我不能让它工作！
我试过了

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_ *值不正确：

| name | num_orders | num_items |
| Foo  |    2       |   2       |
| Bar  |    8       |   8       |

我注意到,如果我尝试一次加入一个表,它的工作原理：

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 |

如何将这两个查询组合在一起？

解决方法

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连接,您不希望这样发生！

所以：

1-你需要很好地了解你的架构.项目是链接到订单,而不是人.

2-您需要为一个人计算不同的订单,否则您将会按订单计数多少.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!