时间戳

首页 > 解决方案 > 如何使用具有多列的 SQL 获取小计？

sql - 如何使用具有多列的 SQL 获取小计？

问题描述

希望生成一份关于销售订单和销售报价的报告，该报告对每个报价和订单进行分组和汇总以提供小计。

它应该为组合在一起的“文档编号”（订单/报价）总计“行金额”。

也可以得到一个总计但不是 100% 的要求。

我曾尝试使用ROLLUP它来制作它，但没有成功。

单击此处获取示例数据和所需结果

SELECT
  CONVERT (VARCHAR(12),SH.[Order Date],107) AS [Posting Date],
  SH.[Salesperson]            AS [Sales Staff],
  SH.[No_]                AS [Document No],
  SH.[Customer No_]                   AS [Cust. No.],
  SH.[Customer Name]              AS [Customer Name],
  SH.[Country]                AS [Country],
  CONVERT (Decimal(10,0),SL.[Quantity])   AS [Qty.],
  SL.[Unit of Measure]            AS [UOM],
  SL.[No_]                AS [Product No.],
  SL.[Description]            AS [Product Description],
  CONVERT (Decimal(10,2),SL.[Unit Price])     AS [Unit Price],
  SUM (CONVERT (Decimal(10,2),SL.[Line Amount]))    AS [Line Amt.],
  SH.[Currency]                       AS [Currency],
FROM    [Sales Header] SH
JOIN    [Sales Line] SL ON SL.[Document No_] = SH.[No_]
GROUP BY    
  SH.[No_], 
  SH.[Salesperson],
  SH.[Order Date],                  
  SH.[Customer No_],
  SH.[Customer Name],
  SH.[Country],
  SH.[Currency],
  SL.[Quantity],                                
  SL.[Unit of Measure],                                             
  SL.[No_],                                                 
  SL.[Description],     
  SH.[Document Type],
  SL.[Unit Price]           
HAVING 
  SH.[Document Type] = '0' OR SH.[Document Type] = '1' AND SL.[Quantity] > '0'

标签： sqlsql-server

解决方案

要实现小计行，您需要在客户端进行多个查询或聚合。通过多个查询，您可以多次查询数据存储或将初始详细信息放在临时表中，然后获取摘要。下面是多次查询。在查询之间存在数据更改的轻微风险。

      -- Line details
  SELECT 
    CONVERT(VARCHAR(12), SH.[Order Date], 107) AS [Posting Date]
    ,SH.[Salesperson] AS [Sales Staff]
    ,SH.[No_] AS [Document No]
    ,SH.[Customer No_] AS [Cust. No.]
    ,SH.[Customer Name] AS [Customer Name]
    ,SH.[Country] AS [Country]
    ,CONVERT(DECIMAL(10, 0), SL.[Quantity]) AS [Qty.]
    ,SL.[Unit of Measure] AS [UOM]
    ,SL.[No_] AS [Product No.]
    ,SL.[Description] AS [Product Description]
    ,CONVERT(DECIMAL(10, 2), SL.[Unit Price]) AS [Unit Price]
    ,SUM(CONVERT(DECIMAL(10, 2), SL.[Line Amount])) AS [Line Amt.]
    ,SH.[Currency] AS [Currency]
    ,'Detail' LineType

FROM [Sales Header] SH
INNER JOIN [Sales Line] SL ON SL.[Document No_] = SH.[No_]
WHERE SH.[Document Type] = '0'
    OR SH.[Document Type] = '1'
    AND SL.[Quantity] > '0'

GROUP BY 
    SH.[Order Date]
    ,SH.[Salesperson]
    ,SH.[No_]
    ,SH.[Customer No_]
    ,SH.[Customer Name]
    ,SH.[Country]
    ,SL.[Quantity]
    ,SL.[Unit of Measure]
    ,SL.[No_]
    ,SL.[Description]
    ,SL.[Unit Price]
    ,SH.[Currency]

UNION ALL


  -- Order Summary
  SELECT [Posting Date]
    , [Sales Staff]
    , [Document No]
    , [Cust. No.]
    , [Customer Name]
    , [Country]
    , NULL AS [Qty.] -- not needed is sub total line
    , NULL AS [UOM] -- not needed is sub total line
    , NULL AS [Product No.] -- not needed is sub total line
    , NULL AS [Product Description] -- not needed is sub total line
    , NULL AS [Unit Price] -- not needed is sub total line
    , SUM(CONVERT(DECIMAL(10, 2), SL.[Line Amount])) OVER (PARTITION BY SH.[No_]) AS [Line Amt.] -- TotalAmountByOrder
    , [Currency]
    ,'Order' LineType
  FROM
  (
  SELECT 
    CONVERT(VARCHAR(12), SH.[Order Date], 107) AS [Posting Date]
    ,SH.[Salesperson] AS [Sales Staff]
    ,SH.[No_] AS [Document No]
    ,SH.[Customer No_] AS [Cust. No.]
    ,SH.[Customer Name] AS [Customer Name]
    ,SH.[Country] AS [Country]
    ,CONVERT(DECIMAL(10, 0), SL.[Quantity]) AS [Qty.]
    ,SL.[Unit of Measure] AS [UOM]
    ,SL.[No_] AS [Product No.]
    ,SL.[Description] AS [Product Description]
    ,CONVERT(DECIMAL(10, 2), SL.[Unit Price]) AS [Unit Price]
    ,SUM(CONVERT(DECIMAL(10, 2), SL.[Line Amount])) AS [Line Amt.]
    ,SH.[Currency] AS [Currency]
FROM [Sales Header] SH
INNER JOIN [Sales Line] SL ON SL.[Document No_] = SH.[No_]
WHERE SH.[Document Type] = '0'
    OR SH.[Document Type] = '1'
    AND SL.[Quantity] > '0'

GROUP BY 
    SH.[Order Date]
    ,SH.[Salesperson]
    ,SH.[No_]
    ,SH.[Customer No_]
    ,SH.[Customer Name]
    ,SH.[Country]
    ,SL.[Quantity]
    ,SL.[Unit of Measure]
    ,SL.[No_]
    ,SL.[Description]
    ,SL.[Unit Price]
    ,SH.[Currency]
) T


UNION ALL


  -- Grand total
  SELECT NULL AS [Posting Date]
    , NULL AS [Sales Staff]
    , NULL AS [Document No]
    , NULL AS [Cust. No.]
    , NULL AS [Customer Name]
    , NULL AS [Country]
    , NULL AS [Qty.] -- not needed is total line
    , NULL AS [UOM] -- not needed is total line
    , NULL AS [Product No.] -- not needed is total line
    , NULL AS [Product Description] -- not needed is total line
    , NULL AS [Unit Price] -- not needed is total line
    , SUM(CONVERT(DECIMAL(10, 2), SL.[Line Amount])) OVER (PARTITION BY 1) AS [Line Amt.] -- TotalAmountAllOrders
    , NULL AS [Currency] -- not needed is total line
    , 'All' LineType
  FROM [Sales Header] SH
    INNER JOIN [Sales Line] SL ON SL.[Document No_] = SH.[No_]
    WHERE SH.[Document Type] = '0'
        OR SH.[Document Type] = '1'
        AND SL.[Quantity] > '0'

初步反应

很高兴看到示例数据或表结构。根据您所拥有的，我对您要达到的目标的最佳猜测如下。

SELECT 
    CONVERT(VARCHAR(12), SH.[Order Date], 107) AS [Posting Date]
    ,SH.[Salesperson] AS [Sales Staff]
    ,SH.[No_] AS [Document No]
    ,SH.[Customer No_] AS [Cust. No.]
    ,SH.[Customer Name] AS [Customer Name]
    ,SH.[Country] AS [Country]
    ,CONVERT(DECIMAL(10, 0), SL.[Quantity]) AS [Qty.]
    ,SL.[Unit of Measure] AS [UOM]
    ,SL.[No_] AS [Product No.]
    ,SL.[Description] AS [Product Description]
    ,CONVERT(DECIMAL(10, 2), SL.[Unit Price]) AS [Unit Price]
    ,SUM(CONVERT(DECIMAL(10, 2), SL.[Line Amount])) AS [Line Amt.]
    ,SH.[Currency] AS [Currency]

    ,SUM(CONVERT(DECIMAL(10, 2), SL.[Line Amount])) OVER (PARTITION BY SH.[No_]) TotalAmountByOrder
    ,SUM(CONVERT(DECIMAL(10, 2), SL.[Line Amount])) OVER (PARTITION BY SH.[Salesperson]) TotalAmountBySalesPerson
    ,SUM(CONVERT(DECIMAL(10, 2), SL.[Line Amount])) OVER (PARTITION BY SH.[Customer No_]) TotalAmountByCustomer
    ,SUM(CONVERT(DECIMAL(10, 2), SL.[Line Amount])) OVER (PARTITION BY 1) TotalAmounAllOrders

FROM [Sales Header] SH
INNER JOIN [Sales Line] SL ON SL.[Document No_] = SH.[No_]
WHERE SH.[Document Type] = '0'
    OR SH.[Document Type] = '1'
    AND SL.[Quantity] > '0'

GROUP BY 
    SH.[Order Date]
    ,SH.[Salesperson]
    ,SH.[No_]
    ,SH.[Customer No_]
    ,SH.[Customer Name]
    ,SH.[Country]
    ,SL.[Quantity]
    ,SL.[Unit of Measure]
    ,SL.[No_]
    ,SL.[Description]
    ,SL.[Unit Price]
    ,SH.[Currency]

  1. 尽管在这种情况下是正确的，但理想情况下属于 where 子句。将其移至 where 子句，我们不再需要它在分组中，并且我们不再需要在 group by 中包含 SH.[Document Type]。

  2. 重新排序您的组。这样做只是为了比较 select 语句中的所有列以确认没有遗漏

  3. 添加了一些聚合列。每个订单、每个销售人员、每个客户和总计的总和。您可以使用这些示例中的任何一个来计算出您需要的确切总数。

推荐阅读