三个表上的 SQL JOIN、GROUP BY 以获取总数

SQL JOIN, GROUP BY on three tables to get totals

提问by Saggi Malachi

I've inherited the following DB design. Tables are:

我继承了以下数据库设计。表是：

customers
---------
customerid  
customernumber

invoices
--------
invoiceid  
amount

invoicepayments
---------------
invoicepaymentid  
invoiceid  
paymentid

payments
--------
paymentid  
customerid  
amount

My query needs to return invoiceid, the invoice amount (in the invoices table), and the amount due (invoice amount minus any payments that have been made towards the invoice) for a given customernumber. A customer may have multiple invoices.

我的查询需要返回给定客户编号的 invoiceid、发票金额（在发票表中）和应付金额（发票金额减去已向发票支付的任何款项）。一个客户可能有多个发票。

The following query gives me duplicate records when multiple payments are made to an invoice:

当对发票进行多次付款时，以下查询为我提供了重复的记录：

SELECT i.invoiceid, i.amount, i.amount - p.amount AS amountdue
FROM invoices i
LEFT JOIN invoicepayments ip ON i.invoiceid = ip.invoiceid
LEFT JOIN payments p ON ip.paymentid = p.paymentid
LEFT JOIN customers c ON p.customerid = c.customerid
WHERE c.customernumber = '100'

How can I solve this?

我该如何解决这个问题？

回答by Saggi Malachi

I am not sure I got you but this might be what you are looking for:

我不确定我找到了你，但这可能是你正在寻找的：

SELECT i.invoiceid, sum(case when i.amount is not null then i.amount else 0 end), sum(case when i.amount is not null then i.amount else 0 end) - sum(case when p.amount is not null then p.amount else 0 end) AS amountdue
FROM invoices i
LEFT JOIN invoicepayments ip ON i.invoiceid = ip.invoiceid
LEFT JOIN payments p ON ip.paymentid = p.paymentid
LEFT JOIN customers c ON p.customerid = c.customerid
WHERE c.customernumber = '100'
GROUP BY i.invoiceid

This would get you the amounts sums in case there are multiple payment rows for each invoice

如果每张发票有多个付款行，这将为您提供金额总和

回答by Nick Silberstein

Thank you very much for the replies!

非常感谢您的回复！

Saggi Malachi, that query unfortunately sums the invoice amount in cases where there is more than one payment. Say there are two payments to a $39 invoice of $18 and $12. So rather than ending up with a result that looks like:

Saggi Malachi，不幸的是，如果付款不止一次，该查询会汇总发票金额。假设一张 39 美元的发票有两笔付款，分别是 18 美元和 12 美元。因此，而不是最终得到如下所示的结果：

1   39.00   9.00

You'll end up with:

你最终会得到：

1   78.00   48.00

Charles Bretana, in the course of trimming my query down to the simplest possible query I (stupidly) omitted an additional table, customerinvoices, which provides a link between customers and invoices. This can be used to see invoices for which payments haven't made.

Charles Bretana，在将我的查询精简为最简单的查询的过程中，我（愚蠢地）省略了一个额外的表 customerinvoices，它提供了客户和发票之间的链接。这可用于查看尚未付款的发票。

After much struggling, I think that the following query returns what I need it to:

经过一番努力，我认为以下查询返回了我需要的内容：

SELECT DISTINCT i.invoiceid, i.amount, ISNULL(i.amount - p.amount, i.amount) AS amountdue
FROM invoices i
LEFT JOIN invoicepayments ip ON i.invoiceid = ip.invoiceid
LEFT JOIN customerinvoices ci ON i.invoiceid = ci.invoiceid
LEFT JOIN (
  SELECT invoiceid, SUM(p.amount) amount
  FROM invoicepayments ip 
  LEFT JOIN payments p ON ip.paymentid = p.paymentid
  GROUP BY ip.invoiceid
) p
ON p.invoiceid = ip.invoiceid
LEFT JOIN payments p2 ON ip.paymentid = p2.paymentid
LEFT JOIN customers c ON ci.customerid = c.customerid
WHERE c.customernumber='100'

Would you guys concur?

你们会同意吗？

回答by PunchyRascal

I have a tip for those, who want to get various aggregated values from the same table.

对于那些想要从同一个表中获取各种聚合值的人，我有一个提示。

Lets say I have table with users and table with points the users acquire. So the connection between them is 1:N (one user, many points records).

假设我有用户表和用户获得的积分表。所以它们之间的连接是1：N（一个用户，多条记录）。

Now in the table 'points' I also store the information about for what did the user get the points (login, clicking a banner etc.). And I want to list all users ordered by SUM(points)AND then by SUM(points WHERE type = x). That is to say ordered by all the points user has and then by points the user got for a specific action (eg. login).

现在在“积分”表中，我还存储了有关用户获得积分的原因（登录、单击横幅等）的信息。我想列出由SUM(points)AND 然后由订购的所有用户SUM(points WHERE type = x)。也就是说，按用户拥有的所有点数排序，然后按用户为特定操作（例如登录）获得的点数排序。

The SQL would be:

SQL 将是：

SELECT SUM(points.points) AS points_all, SUM(points.points * (points.type = 7)) AS points_login
FROM user
LEFT JOIN points ON user.id = points.user_id
GROUP BY user.id

The beauty of this is in the SUM(points.points * (points.type = 7))where the inner parenthesis evaluates to either 0 or 1 thus multiplying the given points value by 0 or 1, depending on wheteher it equals to the the type of points we want.

这样做的好处是在SUM(points.points * (points.type = 7))其中内括号的计算结果为0或1因此由0或1，乘以给定的点值取决于wheteher它等于我们希望点的类型。

回答by Charles Bretana

First of all, shouldn't there be a CustomerId in the Invoices table? As it is, You can't perform this query for Invoices that have no payments on them as yet. If there are no payments on an invoice, that invoice will not even show up in the ouput of the query, even though it's an outer join...

首先，Invoices 表中不应该有一个 CustomerId 吗？实际上，您无法对尚未付款的发票执行此查询。如果发票上没有付款，则该发票甚至不会出现在查询的输出中，即使它是外部联接...

Also, When a customer makes a payment, how do you know what Invoice to attach it to ? If the only way is by the InvoiceId on the stub that arrives with the payment, then you are (perhaps inappropriately) associating Invoices with the customer that paid them, rather than with the customer that ordered them... . (Sometimes an invoice can be paid by someone other than the customer who ordered the services)

另外，当客户付款时，您如何知道将其附加到哪个发票上？如果唯一的方法是通过付款到达的存根上的 InvoiceId，那么您（可能不恰当地）将发票与付款的客户相关联，而不是与订购它们的客户相关联...... （有时发票可由订购服务的客户以外的其他人支付）

回答by Edward

I know this is late, but it does answer your original question.

我知道这已经晚了，但它确实回答了您最初的问题。

/*Read the comments the same way that SQL runs the query
    1) FROM 
    2) GROUP 
    3) SELECT 
    4) My final notes at the bottom 
*/
SELECT 
        list.invoiceid
    ,   cust.customernumber 
    ,   MAX(list.inv_amount) AS invoice_amount/* we select the max because it will be the same for each payment to that invoice (presumably invoice amounts do not vary based on payment) */
    ,   MAX(list.inv_amount) - SUM(list.pay_amount)  AS [amount_due]
FROM 
Customers AS cust 
    INNER JOIN 
Payments  AS pay 
    ON 
        pay.customerid = cust.customerid
INNER JOIN  (   /* generate a list of payment_ids, their amounts, and the totals of the invoices they billed to*/
    SELECT 
            inpay.paymentid AS paymentid
        ,   inv.invoiceid AS invoiceid 
        ,   inv.amount  AS inv_amount 
        ,   pay.amount AS pay_amount 
    FROM 
    InvoicePayments AS inpay
        INNER JOIN 
    Invoices AS inv 
        ON  inv.invoiceid = inpay.invoiceid 
        INNER JOIN 
    Payments AS pay 
        ON pay.paymentid = inpay.paymentid
    )  AS list
ON 
    list.paymentid = pay.paymentid
    /* so at this point my result set would look like: 
    -- All my customers (crossed by) every paymentid they are associated to (I'll call this A)
    -- Every invoice payment and its association to: its own ammount, the total invoice ammount, its own paymentid (what I call list) 
    -- Filter out all records in A that do not have a paymentid matching in (list)
     -- we filter the result because there may be payments that did not go towards invoices!
 */
GROUP BY
    /* we want a record line for each customer and invoice ( or basically each invoice but i believe this makes more sense logically */ 
        cust.customernumber 
    ,   list.invoiceid 
/*
    -- we can improve this query by only hitting the Payments table once by moving it inside of our list subquery, 
    -- but this is what made sense to me when I was planning. 
    -- Hopefully it makes it clearer how the thought process works to leave it in there
    -- as several people have already pointed out, the data structure of the DB prevents us from looking at customers with invoices that have no payments towards them.
*/