Внутреннее соединение с 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 |

Как объединить эти два запроса в одном?

Ответ 1

Имеет смысл присоединиться к элементу с заказами, чем к людям!

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- Вам нужно учитывать разные заказы для одного человека, иначе вы будете считать столько элементов, сколько заказов.

Ответ 2

Как заметил Фрэнк, вам нужно использовать DISTINCT. Кроме того, поскольку вы используете составные первичные ключи (что отлично, BTW), вам нужно убедиться, что вы используете весь ключ в своих соединениях:

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 он соединял каждую строку элемента с каждой строкой порядка для человека.

Ответ 3

Я попытался отличить оба, count (distinct ord.ord_id) как num_order, count (distinct items.item_id) как num items

его работа:)

    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;

Спасибо за Thread, это помогает:)

Ответ 4

Ваше решение почти правильно. Вы можете добавить 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;

Ответ 5

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

Ответ 6

Нужно понять, что делает JOIN или серия JOINs для набора данных. С помощью столбца stee, pe_id из 1, связанного с соответствующим порядком, и элементы на pe_id = 1 дадут вам следующие данные, чтобы "выбрать" из:

[часть таблицы людей] [часть заказов на таблицы] [часть элементов таблицы]

| 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 | Первый заказ | 1 | 1 | 1 | Apple |
| 1 | Foo | 1 | 1 | Первый заказ | 2 | 1 | 1 | Груша |

Соединения, по существу, приходят с декартовым произведением всех таблиц. У вас в основном есть набор данных для выбора и почему вам нужен отдельный счет на orders.ord_id и items.item_id. В противном случае оба счета приведут к 2 - потому что у вас есть 2 строки для выбора.