具有多个表和SUMS的MySQL JOIN

问题描述：

我正在尝试创建一个查询，该查询将从我正在创建的计费系统的四个表中获取信息.我有以下表格:

I am trying to create a query that will take information out of four tables for a billing system that I am creating. I have the following tables:

表格发票

InvoiceID (PK)
ClientID
Date
Status
...

表客户端

ClientID (PK)
ClientName
...

表格InvoiceItem

Table InvoiceItem

ItemID (PK)
InvoiceID
Amount
...

餐桌付款

PaymentID (PK)
InvoiceID
Amount
...

我需要创建一个查询，在其中我可以访问发票"表中的信息以及客户名称以及所有发票项目和与该发票相关的付款的总和.

I need to create a query where I can access information from the Invoice table along with the client name, and the sum of all invoice items and payments associated with the invoice.

我尝试了以下操作:

SELECT 
    Invoice.InvoiceID, 
    Invoice.`Date`, 
    Invoice.Terms, 
    Invoice.DateDue, 
    Invoice.Status, 
    Client.ClinicName, 
    SUM(InvoiceItem.Amount), 
    SUM(Payment.PaymentAmount)
FROM Invoice
JOIN (Client, InvoiceItem, Payment) ON
    (Client.ClientID=Invoice.ClientID AND
     InvoiceItem.InvoiceID=Invoice.InvoiceID AND 
     Payment.InvoiceID=Invoice.InvoiceID)

虽然这种方法有效，但它会将SUM()乘以用于获取总和的记录数(即，如果有两次付款-800,400-它给我(800 + 400)* 2- 2400).我猜想我使用联接的方式有一些问题，老实说，我从来不需要使用多个表的联接，而且我会一直使用GROUP BY，但是我似乎无法正常使用它

And while this kind-of works, it is multiplying the SUM() by the number of records used to get the sum (i.e. if there are two payments - 800,400 - It gives me (800+400)*2 -- 2400). I am guessing that there is something with how I am using the join, and I have honestly never had to use join for more than one table, and I would always use GROUP BY, but I can't seem to get that to work correctly.

更糟糕的是，过去几年我一直迷失于vb.net/MSSQL客户端编程领域，所以我的MySQL相当粗糙.

To make matters worse, I have been lost to the world of vb.net/MSSQL client-side programming for the past several years, so my MySQL is rather rough.

答

您的问题是您无法在单个查询中一次聚合两个独立的表.但是，您可以使用子查询来做到这一点.

Your problem is that you can't aggregate over two independent tables at once in a single query. However you can do it using subqueries.

SELECT Invoice.InvoiceID, Invoice.`Date`, Invoice.Terms, Invoice.DateDue, Invoice.Status, Client.ClinicName, InvoiceItemSum.SumOfAmount, PaymentSum.SumOfPaymentAmount
  FROM Invoice
  INNER JOIN Client ON Client.ClientID = Invoice.ClientID
  INNER JOIN (
    SELECT InvoiceID, SUM(Amount) AS SumOfAmount
      FROM InvoiceItem
      GROUP BY InvoiceID
  ) InvoiceItemSum ON InvoiceItemSum.InvoiceID = Invoice.InvoiceID
  INNER JOIN (
    SELECT InvoiceID, SUM(PaymentAmount) AS SumOfPaymentAmount
    FROM Payment
    GROUP BY InvoiceID
  ) PaymentSum ON PaymentSum.InvoiceID = Invoice.InvoiceID

具有多个表和SUMS的MySQL JOIN

相关推荐