sql-server - MS SQL 中的小计查询

Question

我有一个正在努力解决的问题。

我正在提取本年度一段时间内提出的采购订单和发票清单（连同预算、成本中心等）

查询在这方面工作得很好：

    SELECT TOP (100) PERCENT
            dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C_PERIOD as PERIOD, 
            dbo.T_GENERALLEDGERACCOUNT.C_CODE AS Nominal, 
                        dbo.T_GENERALLEDGERACCOUNT.C_DESCRIPTION AS Cost_Description, 
            dbo.T_COSTCENTRE.C_DESCRIPTION AS Cost_Centre, 
            format (dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C_AMOUNTBASE, 'c', 'en-gb') AS Budget, 
                        format (dbo.ATE_PO_SummedByPeriod.Amount, 'c', 'en-gb') AS Purchase_Orders,
            format (dbo.ATE_InvoicesSummedByPeriod.[Net Amount], 'c', 'en-gb') AS Invoices

FROM                dbo.ATE_PO_SummedByPeriod RIGHT OUTER JOIN dbo.ATE_InvoicesSummedByPeriod RIGHT OUTER JOIN
                        dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET ON dbo.ATE_InvoicesSummedByPeriod.Cost_Centre = dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C_COSTCENTRE AND 
                        dbo.ATE_InvoicesSummedByPeriod.C_PERIOD = dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C_PERIOD AND 
                        dbo.ATE_InvoicesSummedByPeriod.Nominal_ID = dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C__OWNER_ ON 
                        dbo.ATE_PO_SummedByPeriod.Nominal_ID = dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C__OWNER_ AND 
                        dbo.ATE_PO_SummedByPeriod.C_COSTCENTRE = dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C_COSTCENTRE AND 
                        dbo.ATE_PO_SummedByPeriod.PERIOD = dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C_PERIOD LEFT OUTER JOIN
                        dbo.T_COSTCENTRE ON dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C_COSTCENTRE = dbo.T_COSTCENTRE.C_ID RIGHT OUTER JOIN
                        dbo.T_GENERALLEDGERACCOUNT LEFT OUTER JOIN
                        dbo.T_GENERALLEDGERGROUP ON dbo.T_GENERALLEDGERACCOUNT.C_GROUP = dbo.T_GENERALLEDGERGROUP.C_ID ON 
                        dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C__OWNER_ = dbo.T_GENERALLEDGERACCOUNT.C_ID

WHERE 
    (dbo.ATE_InvoicesSummedByPeriod.[Net Amount] > 0) 
    and
    (dbo.T_GENERALLEDGERACCOUNT.C_CODE) IN ('34120')
    and
    (dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C_PERIOD) IN ('202101', '202102', '202103', '202104', '202105', '202106', '202107', '202108', '202109', '202110', '202111', '202112')
ORDER BY PERIOD

这将返回如下内容：

PERIOD  Nominal Cost_Description    Cost_Centre Budget      Purchase_Orders  Invoices
202101  34120   Computer / IT Costs Trailers    £6,543.21   NULL             £1,234.56
202101  34120   Computer / IT Costs Parts       £1,234.56   £6,543.21        £1,234.56
...

我想要的是（在同一个查询中）

1. Budget、Purchase_Orders 和 Invoices 的“总计”显示在表格底部

2. 每个“PERIOD and Nominal”的总计（因此“对于 1 月 21 日。预算的运行总计为 £xxxx，Purchase_Orders 的运行总计为 £xxxx，发票也是如此。

3. 最后......是否有一种“更简洁”的方式来识别我感兴趣的时期（例如）......

   (dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET.C_PERIOD) IN ('2021%')

标签： sql-server