Сумма удваивается при попытке левого соединения таблиц

Question

Сумма удваивается при попытке левого соединения таблиц

У меня есть 3 таблицы

Таблица Клиентов

CustomerID, LastName,

Таблица Транзакций

TransID, CustomerID, Price,

Таблица Платежей

PaymentID, CustomerID, Paid,

Я пытаюсь сделать запрос, чтобы получить общий баланс, объединив CustomerID & Price & Paid затем сделайте Price - Paid и получите полный баланс.

Я попробовал:

SELECT [Customers].LastName, SUM(Transactions.Price) AS [Total Price], SUM(Payments.Paid) AS [Total Paid], SUM(Transactions.Price - Payments.Paid) AS Balance
    FROM ([Customers] LEFT JOIN Payments ON Payments.CustomerID = [Customers].CustomerID) LEFT JOIN Transactions ON Transactions.CustomerID = [Customers].CustomerID
    GROUP BY [Customers].LastName;

Но общая сумма Paid удваивается от реальной сумма платежа (например: Если клиент имеет баланс $ 150 и делает платеж $65, он покажет мне Total Paid = $ 130, и в результате он сделает общий баланс как $20 вместо $ 85).

Результаты Запроса

В качестве дополнительной заметки столбец Total Balance дает мне баланс только в том случае, если уже был сделан платеж. Если платеж еще не был произведен по конкретному идентификатору клиента, то он будет пустым вместо текущего баланса сумма.

Пожалуйста, Помогите!!!

3 2

sql ms-access

3 ответа:

Почему вы получаете двойную, тройную или большую сумму? Речь идет о кардинальности. Если платежи и транзакции являются отношениями 1: M с клиентом; и не имеют никаких отношений между ними, кроме клиента, то записи дублируются, когда вы не хотите, чтобы они были. Предположим, что у нас есть следующие # записей, все связанные с клиентом 1

заказчик: 1

платежи: 2

транзакции: 2

Как вы думаете, что происходит, когда соединение происходит это:
+------------+----------+---------------+-------+-----------+------+--+-------+
| CustomerID | LastName | TransactionID | Price | PaymentID | Paid |  | Total |
+------------+----------+---------------+-------+-----------+------+--+-------+
| 1          | Smith    | 1             | 150   | 1         | 20   |  |       |
| 1          | Smith    | 3             | 30    | 3         | 40   |  |       |
|            |          |               | 180   |           | 60   |  |   120 |
Когда на самом деле происходит следующее: (движок не знает, как привязать платеж к транзакции, поэтому он должен привязать каждую транзакцию ко всем платежам!)
+------------------------------------------------------------------------------
| CustomerID | LastName | TransactionID | Price | PaymentID | Paid |  | Total |
| 1          | Smith    | 1             | 150   | 1         | 20   |  |       |
| 1          | Smith    | 1             | 150   | 3         | 40   |  |       |
| 1          | smith    | 3             | 30    | 1         | 20   |  |       |
| 1          | smith    | 3             | 30    | 3         | 40   |  |   240 |
+------------+----------+---------------+-------+-----------+------+--+-------+
Обратите внимание, что каждый идентификатор транзакции должен быть сопряжен с идентификатором платежа, таким образом, запись и суммы удваиваются, или отключаются, или вчетверо, в зависимости от того, сколько записей требуется для разрешения M:M между транзакцией и платежом. в действительности, поскольку связь не определена, таблица является перекрестным соединением все записи платежей клиентов ко всем записям транзакций клиентов.
Теперь, если бы у вас был способ привязать платежи к транзакциям таким образом, чтобы они были отношением 1:1, тогда нам не понадобились бы подзапросы. Однако, поскольку я сомневаюсь, что вы когда-либо сможете это сделать (клиенты могут делать частичные платежи; таким образом, вам нужно поддерживать 1:M), я бы рекомендовал суммировать в встроенном представлении перед объединениями.
Когда вы соединяете эти три таблицы, вы получаете 4 записи, а не 2. 1 запись клиента * 2 записи платежей * 2 сделки = 4. Теперь каждый платеж дублируется один раз для каждой записи в транзакции, таким образом, платеж в размере $10 происходит дважды, если существует 2 транзакции. Чтобы решить эту проблему, вы должны суммировать записи перед соединениями; таким образом, создается соотношение 1:1:1, и искусственное завышенное количество/итоги уходят.

В качестве примечания: вам нужно nz (взять первое ненулевое значение) для общей цены и общей суммы, уплаченной за ситуации, которые не включают платежи или транзакции для клиента. Сказать нет платежи произошли для клиента один, когда вы берете 150+30, вы получите 180 вычитание null, и вы получите null. таким образом, NZ важно обрабатывать ситуации, когда платеж или транзакция не произошли для клиента.
SELECT c.LastName
     , nz(T.[Total Price],0)
     , nz(P.[Total Paid],0)
     , nz(T.[Total Price],0)- nz(P.[Total Paid],0) AS Balance
FROM [Customers] c
LEFT JOIN (SELECT sum(Payments.paid) as [Total Paid], customerID 
           FROM payments 
           GROUP BY customerID ) p
  ON P.CustomerID = c.CustomerID
LEFT JOIN (SELECT sum(Transactions.price) as [Total Price], customerID  
           FROM Transactions 
           GROUP BY customerID) t
  ON t.CustomerID = c.CustomerID

0

Вот запрос, который полностью соответствует вашим требованиям

select a.lastname,a.tot_payment,b.tot_trans,(b.tot_trans-a.tot_payment) as balance 
from (select c.id,lastname,sum(p.price) tot_payment--,sum(t.price) 
from customers c
left join payments p on c.id = p.custid
group by lastname,c.id) a
join 
(select c.id,sum(t.price) tot_trans
from customers c
left join transactions t on t.custid = c.id
group by c.id)b
on a.id = b.id

0

MKR · Accepted Answer · 2017-08-07 22:59:06

Вы можете попробовать следующий запрос:

SELECT [A].LastName, (SUM(A.PaidAmt) - SUM(PriceAmt) ) AS Amt

FROM (
SELECT
    [Customers].LastName, (Payments.Paid) as PaidAmt , 0 AS PriceAmt

FROM [Customers]
LEFT JOIN Payments
    ON Payments.CustomerID = [Customers].CustomerID


UNION

SELECT
    [Customers].LastName, 0 as PaidAmt , (Transactions.Price) AS PriceAmt
FROM [Customers]
LEFT JOIN Transactions
    ON Transactions.CustomerID = [Customers].CustomerID
) A
GROUP BY A.LastName;

Это должно сработать.