sql – 使用多个JOINS. SUM()产生错误的值

我在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(正好是两倍).我的理论是,由于某种原因,查询是两次“汇总”付款金额列.任何人都可以为我阐明这一点吗？

谢谢您的帮助！

解决方法

如果您在没有分组依据的情况下运行查询,您会看到某些付款有多行.那是因为你也加入了订单商品.对于orderitem和payment的每个组合,结果集将包含一行.

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

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

这将确保多个订单项的付款不会多次汇总.

原文链接：/mssql/84317.html

sql – 使用多个JOINS. SUM()产生错误的值

解决方法

猜你在找的MsSQL相关文章