Внутреннее объединение с количеством () на трех таблицах
Простой и быстрый вопрос, у меня есть те таблицы:
//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 |
Как объединить те два запроса в одном?
3 ответа
Более логично объединять элемент с заказами, чем с людьми!
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- Вам нужно подсчитывать отдельные заказы для одного человека, иначе вы будете считать столько же предметов, сколько заказов.
Как указал Фрэнк, вам нужно использовать 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
Без I.ord_id = O.ord_id он присоединял каждую строку элемента к каждая строка заказа на человека.
Ваше решение почти верное. Вы можете добавить 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;