MS SQL采购订单发票查询添加总计、周期小计及筛选优化问题

完整解决方案代码 #

WITH raw_data AS (
    SELECT
        gb.C_PERIOD as PERIOD, 
        ga.C_CODE AS Nominal, 
        ga.C_DESCRIPTION AS Cost_Description, 
        cc.C_DESCRIPTION AS Cost_Centre, 
        -- 保留原始数值用于聚合，不提前格式化
        gb.C_AMOUNTBASE AS Budget_Val, 
        po.Amount AS PO_Val,
        inv.[Net Amount] AS Inv_Val
    FROM dbo.ATE_PO_SummedByPeriod po
    RIGHT OUTER JOIN dbo.ATE_InvoicesSummedByPeriod inv 
        ON inv.Cost_Centre = po.C_COSTCENTRE 
        AND inv.C_PERIOD = po.PERIOD 
        AND inv.Nominal_ID = po.Nominal_ID
    RIGHT OUTER JOIN dbo.T_GENERALLEDGERACCOUNT_PERIODBUDGET gb
        ON inv.Cost_Centre = gb.C_COSTCENTRE 
        AND inv.C_PERIOD = gb.C_PERIOD 
        AND inv.Nominal_ID = gb.C__OWNER_
        AND po.Nominal_ID = gb.C__OWNER_ 
        AND po.C_COSTCENTRE = gb.C_COSTCENTRE 
        AND po.PERIOD = gb.C_PERIOD
    LEFT OUTER JOIN dbo.T_COSTCENTRE cc 
        ON gb.C_COSTCENTRE = cc.C_ID 
    RIGHT OUTER JOIN dbo.T_GENERALLEDGERACCOUNT ga
        ON gb.C__OWNER_ = ga.C_ID
    LEFT OUTER JOIN dbo.T_GENERALLEDGERGROUP gg
        ON ga.C_GROUP = gg.C_ID 
    WHERE 
        inv.[Net Amount] > 0
        AND ga.C_CODE = '34120'
        -- 简化2021年全年筛选
        AND gb.C_PERIOD LIKE '2021%'
)
SELECT
    -- 区分行类型显示对应标识
    CASE 
        WHEN GROUPING_ID(PERIOD, Nominal, Cost_Description, Cost_Centre) = 3 THEN '小计'
        WHEN GROUPING_ID(PERIOD, Nominal, Cost_Description, Cost_Centre) = 15 THEN '总计'
        ELSE PERIOD 
    END AS PERIOD,
    CASE 
        WHEN GROUPING_ID(PERIOD, Nominal, Cost_Description, Cost_Centre) >=3 THEN ''
        ELSE Nominal 
    END AS Nominal,
    CASE 
        WHEN GROUPING_ID(PERIOD, Nominal, Cost_Description, Cost_Centre) = 3 THEN CONCAT(PERIOD, ' 汇总')
        WHEN GROUPING_ID(PERIOD, Nominal, Cost_Description, Cost_Centre) = 15 THEN '全量汇总'
        ELSE Cost_Description 
    END AS Cost_Description,
    CASE 
        WHEN GROUPING_ID(PERIOD, Nominal, Cost_Description, Cost_Centre) = 3 THEN ''
        WHEN GROUPING_ID(PERIOD, Nominal, Cost_Description, Cost_Centre) = 15 THEN ''
        ELSE Cost_Centre 
    END AS Cost_Centre,
    FORMAT(SUM(Budget_Val), 'c', 'en-gb') AS Budget,
    FORMAT(SUM(PO_Val), 'c', 'en-gb') AS Purchase_Orders,
    FORMAT(SUM(Inv_Val), 'c', 'en-gb') AS Invoices
FROM raw_data
-- 多层级分组汇总
GROUP BY GROUPING SETS (
    -- 原始明细行，和原有查询输出一致
    (PERIOD, Nominal, Cost_Description, Cost_Centre),
    -- PERIOD+Nominal 分组小计
    (PERIOD, Nominal),
    -- 全量总计
    ()
)
-- 排序规则：明细在前、小计在同周期末尾、总计在最后
ORDER BY 
    CASE WHEN GROUPING_ID(PERIOD, Nominal, Cost_Description, Cost_Centre) = 15 THEN 2 ELSE 1 END,
    PERIOD,
    GROUPING_ID(PERIOD, Nominal, Cost_Description, Cost_Centre)

各需求实现说明 #

1. 全量总计+周期科目小计实现：使用SQL Server原生GROUPING SETS语法实现多层级聚合，无需写多个查询用UNION ALL拼接，性能和可读性更好。GROUPING_ID函数用于区分不同层级的汇总行，给小计、总计行加上标识，同时控制展示顺序符合使用习惯。注意金额格式化操作放到聚合之后执行，避免提前转成字符串无法求和。
2. 周期筛选简化实现：你的C_PERIOD是YYYYMM格式的字符串，直接用LIKE '2021%'即可匹配2021年全部12个周期，完全可以替代枚举12个值的写法。如果C_PERIOD是数值类型，换成C_PERIOD / 100 = 2021即可实现相同效果。

内容的提问来源于stack exchange，提问作者James Davis