简单快捷的问题,我有这些表：
//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-您需要为一个人计算不同的订单,否则您将会按订单计数多少.
