sql - 使用多个 JOINS。 SUM() 产生错误值

Question

我在 SQL 查询中获取一些基本发票信息，并在同一查询中计算订单总计和付款总计。这是我迄今为止所拥有的:

SELECT
    orders.billerID, 
    orders.invoiceDate, 
    orders.txnID, 
    orders.bName, 
    orders.bStreet1, 
    orders.bStreet2, 
    orders.bCity, 
    orders.bState, 
    orders.bZip, 
    orders.bCountry, 
    orders.sName, 
    orders.sStreet1, 
    orders.sStreet2, 
    orders.sCity, 
    orders.sState, 
    orders.sZip, 
    orders.sCountry, 
    orders.paymentType, 
    orders.invoiceNotes, 
    orders.pFee, 
    orders.shipping, 
    orders.tax, 
    orders.reasonCode, 
    orders.txnType, 
    orders.customerID, 
    customers.firstName AS firstName, 
    customers.lastName AS lastName, 
    customers.businessName AS businessName, 
    orderStatus.statusName AS orderStatus, 
    SUM((orderItems.itemPrice * orderItems.itemQuantity))
      + orders.shipping + orders.tax AS orderTotal, 
    SUM(payments.amount) AS totalPayments                       <-- this sum
FROM
    orders 
    LEFT JOIN customers ON orders.customerID = customers.id 
    LEFT JOIN orderStatus ON orders.orderStatus = orderStatus.id
    LEFT JOIN payments ON payments.orderID = orders.id          <-- this join
    LEFT JOIN orderItems ON orderItems.orderID = orders.id 

除了totalPayments 列之外，查询中的所有内容都非常出色。数据库中有一笔付款，其值为 (10.00)。查询提供的值是 20.00(正好是双倍)。我的理论是，由于某种原因，查询对付款金额列进行了两次“求和”。谁能帮我解释一下这个问题吗？

感谢您的帮助!

Answer 1

如果您在没有分组依据的情况下运行查询，您会看到某些付款有多行。那是因为您还加入了订单商品。结果集将包含订单项目和付款的每个组合的一行。

一种解决方案是将总和更改为:

,    <earlier columns>    
,    (   select SUM(payments.amount) 
         from payments 
         where payments.orderID = orders.id
     ) AS totalPayments
,    <later columns>

这将确保多个订单项的付款不会多次求和。