计算每组的小计

Question

我需要来自多表的报告我使用此查询（SQL Server）

Select  CASE When ([bills].[BT] ='0' and [bills].[T] = 1 )Then 'Purchas1'
When([bills].[BT] ='0' and [bills].[T] = 3 ) Then 'Output'
When([bills].[BT] ='0' and [bills].[T] = 4 ) Then 'Input' 
When [bills].[BT] ='1'  Then  'ٍSales'
When [bills].[BT] = '2' Then  'Prch2'
When [bills].[BT] = '3' Then  'ٍSales2'
When [bills].[BT] = '4'  Then 'SInput' 
END AS BillType,
[mat].[Name] as Product,
[mat].[Code], [store].[Name], 
SUM( [billInfo].[qty]) as Qtys 
from [mat],[billInfo000],[store],[bu],[bills] 
Where [bu].[TG] =[bills].[g]
and [billInfo].[ParentGUID] =[bu].[g] 
and [billInfo].[StoreGUID] =[store].[g] 
and [billInfo].[MatGUID] = [mat].[g] 
Group by [bills].[BT],[bills].[T],[mat].[Name], 
[mat].[Code],[store].[Name] ,[mat].[qty]

我想要的是在每个组后添加一行进行计算 如果它是相同的产品和相同的代码和商店我需要 收集 buy1+Input+Prch2+SInput 减去 Sales、Output、Sales2 像这样：

BillType | Product | Code | Name | Qtys 
--------   -------   ----   ----   ---- 
Purchas1   Pro1      001    Main    150
Output     Pro1      001    Main    10
Sales      Pro1      001    Main    30
Purch2     Pro1      001    Main    50
Balance    Pro1      001    Main    160
Output     Pro1      001    Branch  10
Sales      Pro1      001    Branch  10
Balance    Pro1      001    Brabch  -20

谢谢

Answer 1

不是 ROLLUP，而是使用 WITH 语句和 UNION 也可以达到同样的效果。

其要点是

• 使用 WITH 语句
• SELECT all from q 将原始查询存储在 q
• 中进一步细化再次从 q 中使用 GROUP BY 来计算余额
• UNION 将结果组合在一起

SQL Server 2000

SELECT  *
FROM    (
          SELECT  CASE  WHEN ([bills].[BT] ='0' and [bills].[T] = 1 ) THEN 'Purchas1'
                        WHEN ([bills].[BT] ='0' and [bills].[T] = 3 ) THEN 'Output'
                        WHEN ([bills].[BT] ='0' and [bills].[T] = 4 ) THEN 'Input' 
                        WHEN [bills].[BT] = '1' THEN 'Sales'
                        WHEN [bills].[BT] = '2' THEN 'Prch2'
                        WHEN [bills].[BT] = '3' THEN 'Sales2'
                        WHEN [bills].[BT] = '4' THEN 'SInput' 
                  END AS BillType
                  , [mat].[Name] AS Product
                  , [mat].[Code]
                  , [store].[Name]
                  , SUM([billInfo].[qty]) AS Qtys 
          FROM    [mat]
                  INNER JOIN [billInfo000] ON [billInfo000].[MatGUID] = [mat].[g]
                  INNER JOIN [store] ON [store].[g] = [billInfo0001].[StoreGUID]
                  INNER JOIN [bu] ON [bu].[g] = [billInfo000].[ParentGUID]
                  INNER JOIN [bills] ON [bills].[g] = [bu].[TG]
          GROUP BY
                [bills].[BT]
                , [bills].[T]
                , [mat].[Name]
                , [mat].[Code]
                , [store].[Name]
                , [mat].[qty]
        ) bt
UNION ALL
SELECT  'Balance'
        , Product
        , Code
        , Name
        , SUM(
            CASE  WHEN BillType = 'Purchas1' THEN Qtys
                  WHEN BillType = 'Output' THEN Qtys * -1
                  WHEN BillType = 'Sales' THEN Qtys * -1
                  WHEN BillType = 'Purch2' THEN Qtys
            END)
FROM    (
          SELECT  CASE  WHEN ([bills].[BT] ='0' and [bills].[T] = 1 ) THEN 'Purchas1'
                        WHEN ([bills].[BT] ='0' and [bills].[T] = 3 ) THEN 'Output'
                        WHEN ([bills].[BT] ='0' and [bills].[T] = 4 ) THEN 'Input' 
                        WHEN [bills].[BT] = '1' THEN 'Sales'
                        WHEN [bills].[BT] = '2' THEN 'Prch2'
                        WHEN [bills].[BT] = '3' THEN 'Sales2'
                        WHEN [bills].[BT] = '4' THEN 'SInput' 
                  END AS BillType
                  , [mat].[Name] AS Product
                  , [mat].[Code]
                  , [store].[Name]
                  , SUM([billInfo].[qty]) AS Qtys 
          FROM    [mat]
                  INNER JOIN [billInfo000] ON [billInfo000].[MatGUID] = [mat].[g]
                  INNER JOIN [store] ON [store].[g] = [billInfo0001].[StoreGUID]
                  INNER JOIN [bu] ON [bu].[g] = [billInfo000].[ParentGUID]
                  INNER JOIN [bills] ON [bills].[g] = [bu].[TG]
          GROUP BY
                [bills].[BT]
                , [bills].[T]
                , [mat].[Name]
                , [mat].[Code]
                , [store].[Name]
                , [mat].[qty]
        ) balance
GROUP BY
        Product
        , Code
        , Name

SQL Server 2005+

;WITH q AS (
  SELECT  CASE  WHEN ([bills].[BT] ='0' and [bills].[T] = 1 ) THEN 'Purchas1'
                WHEN ([bills].[BT] ='0' and [bills].[T] = 3 ) THEN 'Output'
                WHEN ([bills].[BT] ='0' and [bills].[T] = 4 ) THEN 'Input' 
                WHEN [bills].[BT] = '1' THEN 'Sales'
                WHEN [bills].[BT] = '2' THEN 'Prch2'
                WHEN [bills].[BT] = '3' THEN 'Sales2'
                WHEN [bills].[BT] = '4' THEN 'SInput' 
          END AS BillType
          , [mat].[Name] AS Product
          , [mat].[Code]
          , [store].[Name]
          , SUM([billInfo].[qty]) AS Qtys 
  FROM    [mat]
          INNER JOIN [billInfo000] ON [billInfo000].[MatGUID] = [mat].[g]
          INNER JOIN [store] ON [store].[g] = [billInfo0001].[StoreGUID]
          INNER JOIN [bu] ON [bu].[g] = [billInfo000].[ParentGUID]
          INNER JOIN [bills] ON [bills].[g] = [bu].[TG]
  GROUP BY
        [bills].[BT]
        , [bills].[T]
        , [mat].[Name]
        , [mat].[Code]
        , [store].[Name]
        , [mat].[qty]
)
SELECT  *
FROM    q
UNION ALL
SELECT  'Balance'
        , Product
        , Code
        , Name
        , SUM(
            CASE  WHEN BillType = 'Purchas1' THEN Qtys
                  WHEN BillType = 'Output' THEN Qtys * -1
                  WHEN BillType = 'Sales' THEN Qtys * -1
                  WHEN BillType = 'Purch2' THEN Qtys
            END)
FROM    q
GROUP BY
        Product
        , Code
        , Name