时间戳

首页 > 解决方案 > 如何在每个发票编号的多个项目中使用 GROUP by？

sql-server - 如何在每个发票编号的多个项目中使用 GROUP by？

问题描述

主表包含一系列具有多个项目的发票。

显然，如果一张发票有多个项目，结果会为同一个发票编号产生多条记录。因此，金额的总和包括同一发票编号的多次重复。

一直在绞尽脑汁来制造一个 SQL 查询，以同时创建一个总和，即使它有不同的行项目。

有任何想法吗 ？当前结果如下所示：

当前的

但它应该只是该发票编号下的 1 行。

我得到的代码是：

SELECT
  STUFF(dbo.Invoice.CustomerPO, 1, 2, '') AS [Customer PO],
  '' AS Blank,
  ISNULL(dbo.Invoice.CredInvoiceNo, dbo.Invoice.InvoiceNo) AS [Invoice Number],
  dbo.InvoiceDetails.ExtendedPrice AS Net,
  dbo.InvoiceDetails.TotalTaxes AS GST,
  dbo.InvoiceDetails.ExtendedPrice + dbo.InvoiceDetails.TotalTaxes AS Gross,
  dbo.Invoice.EntryDate AS [Invoice Date],
  dbo.Invoice.InvoiceID,
  dbo.Customers.CustomerID,
  dbo.ItemSpecs.ItemSpecID
FROM
  dbo.SalesOrder WITH (NOLOCK)
  RIGHT OUTER JOIN dbo.CustomerBillTo
  RIGHT OUTER JOIN dbo.GLAccounts AS GLAccounts_1
  RIGHT OUTER JOIN dbo.GLAccounts AS GLAccounts_2
  RIGHT OUTER JOIN dbo.Customers WITH (NOLOCK)
  LEFT OUTER JOIN dbo.Territories WITH (NOLOCK) ON dbo.Customers.TerritoryID = dbo.Territories.TerritoryID
  RIGHT OUTER JOIN dbo.Invoice WITH (NOLOCK) ON dbo.Customers.CustomerID = dbo.Invoice.CustomerID
  INNER JOIN dbo.InvoiceDetails WITH (NOLOCK)
  LEFT OUTER JOIN dbo.UOMs WITH (NOLOCK) ON dbo.InvoiceDetails.PriceUOMID = dbo.UOMs.UOMID
  LEFT OUTER JOIN dbo.SalesOrderDetails WITH (NOLOCK) ON dbo.InvoiceDetails.SalesOrderDetailID = dbo.SalesOrderDetails.SalesOrderDetailID ON dbo.Invoice.InvoiceID = dbo.InvoiceDetails.InvoiceID ON GLAccounts_2.GLAccountID = dbo.InvoiceDetails.CogsGLAccountID ON GLAccounts_1.GLAccountID = dbo.InvoiceDetails.InvGLAccountID
  LEFT OUTER JOIN dbo.GLAccounts
  LEFT OUTER JOIN dbo.CurrencyCodes ON dbo.GLAccounts.CurrencyCodeID = dbo.CurrencyCodes.CurrencyCodeID ON dbo.InvoiceDetails.SalesGLAccountID = dbo.GLAccounts.GLAccountID ON dbo.CustomerBillTo.CustomerBillToID = dbo.Invoice.CustomerBillToID ON dbo.SalesOrder.SalesOrderID = dbo.SalesOrderDetails.SalesOrderID
  LEFT OUTER JOIN (
    SELECT
      dbo.ARPaymentDetails.InvoiceID,
      MAX(dbo.ARPayment.EntryDate) AS LastPaymentDate
    FROM
      dbo.ARPaymentDetails
      INNER JOIN dbo.ARPayment ON dbo.ARPaymentDetails.ARPaymentID = dbo.ARPayment.ARPaymentID
    WHERE
      (dbo.ARPayment.Posted = 1)
    GROUP BY
      dbo.ARPaymentDetails.InvoiceID
  ) AS ARPayments ON dbo.Invoice.InvoiceID = ARPayments.InvoiceID
  LEFT OUTER JOIN dbo.listToTable(
    'Partially Shipped,Partially Shipped and/or Partially Invoiced,Shipped,Shipped + Partially Invoiced,Invoiced,Cancelled',
    ','
  ) AS SalesOrderStatus ON dbo.SalesOrderDetails.SOStatus = SalesOrderStatus.Position
  LEFT OUTER JOIN dbo.ItemSpecs WITH (NOLOCK) ON dbo.InvoiceDetails.ItemSpecID = dbo.ItemSpecs.ItemSpecID
  LEFT OUTER JOIN dbo.ShippingDetails WITH (NOLOCK) ON dbo.InvoiceDetails.ShippingDetailID = dbo.ShippingDetails.ShippingDetailID
  LEFT OUTER JOIN dbo.SalesReps WITH (NOLOCK) ON dbo.SalesOrder.SalesRepID = dbo.SalesReps.SalesRepID
  LEFT OUTER JOIN dbo.Shipping WITH (NOLOCK) ON dbo.ShippingDetails.ShippingID = dbo.Shipping.ShippingID
WHERE
  (dbo.Customers.CustomerNo = 'omx')
GROUP BY
  dbo.Invoice.CredInvoiceNo,
  dbo.Invoice.InvoiceNo,
  STUFF(dbo.Invoice.CustomerPO, 1, 2, ''),
  dbo.InvoiceDetails.ExtendedPrice,
  dbo.InvoiceDetails.TotalTaxes,
  dbo.InvoiceDetails.ExtendedPrice + dbo.InvoiceDetails.TotalTaxes,
  dbo.Invoice.EntryDate,
  dbo.Invoice.InvoiceID,
  dbo.Customers.CustomerID,
  dbo.ItemSpecs.ItemSpecID
ORDER BY
  [Invoice Number] DESC

标签： sql-server

解决方案

正如@ZLK 所建议的那样，如果您取出组中引用 InvoiceDetail 的任何内容，并在选择列表中的每个 InvoiceDetail 列周围添加一个 sum()，那么您将得到您想要的。

所以选择列表看起来像：

SELECT  STUFF(dbo.Invoice.CustomerPO, 1, 2, '') AS [Customer PO], '' AS Blank, ISNULL(dbo.Invoice.CredInvoiceNo, dbo.Invoice.InvoiceNo) AS [Invoice Number], 
    sum(dbo.InvoiceDetails.ExtendedPrice AS Net, 
    sum(dbo.InvoiceDetails.TotalTaxes) AS GST, 
    sum(dbo.InvoiceDetails.ExtendedPrice + dbo.InvoiceDetails.TotalTaxes) AS Gross,
    dbo.Invoice.EntryDate AS [Invoice Date], dbo.Invoice.InvoiceID, dbo.Customers.CustomerID,

和分组：

GROUP BY ISNULL(dbo.Invoice.CredInvoiceNo, dbo.Invoice.InvoiceNo), STUFF(dbo.Invoice.CustomerPO, 1, 2, ''), 
dbo.Invoice.EntryDate, dbo.Invoice.InvoiceID, dbo.Customers.CustomerID

推荐阅读