具有多个表和 SUMS 的 MySQL JOIN

Question

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

表格发票

InvoiceID (PK)
ClientID
Date
Status
...

表格客户端

ClientID (PK)
ClientName
...

表格发票项目

ItemID (PK)
InvoiceID
Amount
...

表支付

PaymentID (PK)
InvoiceID
Amount
...

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

我尝试了以下方法:

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)。我猜我使用 join 的方式有问题，老实说，我从来没有对多个表使用过 join，而且我总是使用 GROUP BY，但我似乎无法让它正常工作.

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

Answer 1

您的问题是您不能在单个查询中同时聚合两个独立的表。但是，您可以使用子查询来完成。

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