Объединение 2 таблиц отношений «многие ко многим»

Проблема:

Найдите чистый баланс общей суммы заказа и общей суммы платежей для каждого клиента.

Задействовано 4 таблицы: OrderDetails, Orders, Payments и Customer.

Общая сумма заказа = количество заказа * цена каждого [в OrderDetails]

Общий платеж = сумма разных платежей за один и тот же заказ.

Customers связаны с Payments, а Orders с CustomerNumber. Orders связаны с OrderDetails через OrderNumber.

Я попытался объединить 4 таблицы с помощью функции INNER JOIN.

SELECT
    c.customername, 
    SUM(od.quantityordered * od.priceeach) - SUM(p.amount) AS Net_Balance
FROM 
    (
        (
            orderdetails od 
            INNER JOIN orders o ON od.ordernumber = o.ordernumber
        )  
        INNER JOIN customers c ON o.customernumber = c.customernumber
    )      
    INNER JOIN payments p ON c.customernumber = p.customernumber
GROUP BY c.customername;

Ожидаемые результаты должны быть равны 0 почти для всех клиентов.

Однако то, что у меня есть, - это общая сумма заказа и общая оплата, умноженные на некоторые константы. В частности, показанная общая сумма платежа умножается на количество платежей по каждому заказу.

У кого-нибудь есть идеи, как спасти мою жизнь?


sql many-to-many ms-access nested-queries
person MinhDang1010    schedule 31.03.2019    source источник
comment
Что происходит, так это то, что вы объединяете позиции заказов с платежами клиентов. Но все, что у них общего, — это порядок, к которому они относятся. Единый платеж не относится к одной позиции заказа. Что вы действительно хотите сделать, так это соединить итоги заказов клиентов (которые вы получаете, агрегируя позиции заказов) с платежами клиентов (которые вы получаете, агрегируя платежи). См. ответ GMB по этому поводу.   -  person Thorsten Kettner    schedule 31.03.2019
comment
Почему вы удаляете большую часть содержания вашего вопроса после принятия ответа? Я откатился на предыдущую версию.   -  person GMB    schedule 01.04.2019
comment
Пожалуйста, в вопросах кода дайте минимальный воспроизводимый пример - вырезайте, вставляйте и выполняйте код, а также желаемый результат, четкую спецификацию и объяснение. Минимальный означает добавление минимального проблемного кода к минимальному рабочему коду. Поэтому дайте минимальный код, который вы показываете, делает то, что вы ожидаете, и минимальный код с первым местом, где вы ошибетесь. (Основы отладки.)   -  person philipxy    schedule 01.04.2019
comment
Это часто задаваемые вопросы. Прежде чем рассматривать публикацию, пожалуйста, всегда гуглите свое сообщение об ошибке или много четких, кратких и точных формулировок вашего вопроса/проблемы/цели, с вашими конкретными строками/именами и без них, и читайте много ответов. Если вы публикуете вопрос, используйте одну фразу в качестве заголовка. См. раздел Как задать вопрос и тексты при наведении указателя мыши на стрелку голосования. PS Вероятно, это распространенная ошибка, когда люди хотят присоединиться к некоторым агрегациям (каждая из которых, возможно, включает в себя объединение), но они ошибочно пытаются выполнить все объединение, а затем все агрегирование. PS Googe 'сайт: stackoverflow.com sql множественные агрегации неверны' - много попаданий.   -  person philipxy    schedule 01.04.2019
comment
Возможный дубликат странного повторяющегося поведения от GROUP_CONCAT двух LEFT JOINs GROUP_BY   -  person philipxy    schedule 01.04.2019

Ответы (2)


arrow_upward
1
arrow_downward

Это типичная проблема при работе с отношениями N-M. Чтобы решить эту проблему, одним из решений является перемещение агрегации в подзапросы:

SELECT c.customername, o.amt - p.amt AS Net_Balance
FROM customers c 
INNER JOIN (
    SELECT ord.customernumber, SUM(det.quantityordered * det.priceeach) as amt
    FROM orders ord
    INNER JOIN orderdetails det ON ord.ordernumber = det.ordernumber
    GROUP BY ord.customernumber
) o ON o.customernumber = c.customernumber
INNER JOIN (
    SELECT customernumber, SUM(amount) as amt
    FROM payments
    GROUP BY customernumber
) p ON p.customernumber = c.customernumber
person GMB    schedule 31.03.2019

arrow_upward
0
arrow_downward 

SELECT c.customername, SUM(od.quantityordered*od.priceeach) as ordersum ,  SUM(p.amount) as paymentsum'

Каков результат двух столбцов?
Это то, что вы хотите?

person Abdelrahman Maharek    schedule 31.03.2019