SQL Server中如何实现Flag启用、alc%与vat相同的分组小计？

要生成flag为true、alc%和vat相同的产品小计，你可以用SQL的GROUPING SETS语法，它能让你同时返回明细行和指定分组的汇总行。

修改后的SQL示例 #

SELECT 
    -- 小计行显示"小计"，明细行保留商品描述
    CASE WHEN GROUPING(it.itemdescription) = 1 THEN '小计' ELSE it.itemdescription END AS item,
    it.vatpercent AS vat,
    SUM(it.AQty) AS firstquant,
    SUM(it.bQty) AS secquant,
    -- 小计行的volume和alc%留空，避免混淆
    CASE WHEN GROUPING(it.itemdescription) = 1 THEN NULL ELSE itm.userdefnum02 END AS volume,
    CASE WHEN GROUPING(it.itemdescription) = 1 THEN NULL ELSE itm.userdefnum01 END AS [alc%],
    -- 汇总酒精总量：直接聚合明细计算值，不用单条产品属性
    SUM(itm.userdefnum02 * itm.USERDEFNUM01 * it.aQty) AS alclit,
    SUM(12.25 * itm.userdefnum02 * itm.USERDEFNUM01 * it.aQty) AS tax,
    itm.HEUSERDEFBOOL01 AS flag,
    -- 标记是否为小计行（1=小计，0=明细）
    GROUPING(it.itemdescription) AS is_subtotal
FROM centlines it
INNER JOIN items itm ON itm.code = it.itemcode
INNER JOIN commercialentries ce ON ce.dentid = it.dentid
GROUP BY 
    GROUPING SETS (
        -- 第一组：原有明细分组，返回单产品统计行
        (itm.userdefnum01, it.vatpercent, itm.HEUSERDEFBOOL01, it.itemdescription, itm.userdefnum02, ITM.code),
        -- 第二组：按flag、alc%、vat汇总，生成小计行
        (itm.userdefnum01, it.vatpercent, itm.HEUSERDEFBOOL01)
    )
-- 过滤掉flag=false的小计行（仅保留启用flag的产品小计）
HAVING NOT (GROUPING(it.itemdescription) = 1 AND itm.HEUSERDEFBOOL01 = 0)
ORDER BY 
    itm.HEUSERDEFBOOL01 DESC,
    itm.userdefnum01,
    it.vatpercent,
    GROUPING(it.itemdescription) -- 让明细行在前，小计行在后

关键说明 #

1. GROUPING SETS：定义两个分组维度，同时返回明细行和指定的汇总行，满足你既要明细统计又要小计的需求。
2. GROUPING函数：用来区分小计行和明细行，返回1表示当前字段被聚合，属于小计行，方便后续识别或格式化。
3. 汇总字段计算：小计行的alclit和tax必须用SUM()包裹明细的计算逻辑，不能直接使用单条产品的volume和alc%，否则会取到随机的产品属性值，导致汇总错误。
4. HAVING过滤：剔除flag=false的小计行，只保留你需要的启用flag的产品小计。

如果你的数据库不支持GROUPING SETS（比如旧版MySQL），可以用UNION ALL拆分查询后合并结果：

-- 先查询明细行
SELECT 
    it.itemdescription AS item,
    it.vatpercent AS vat,
    SUM(it.AQty) AS firstquant,
    SUM(it.bQty) AS secquant,
    itm.userdefnum02 AS volume,
    itm.userdefnum01 AS [alc%],
    SUM(itm.userdefnum02*itm.USERDEFNUM01*it.aQty) AS alclit,
    SUM(12.25*itm.userdefnum02*itm.USERDEFNUM01*it.aQty) AS tax,
    itm.HEUSERDEFBOOL01 AS flag,
    0 AS is_subtotal
FROM centlines it
INNER JOIN items itm ON itm.code=it.itemcode
INNER JOIN commercialentries ce ON ce.dentid=it.dentid
GROUP BY itm.userdefnum01,it.vatpercent,itm.HEUSERDEFBOOL01 ,it.itemdescription,itm.userdefnum02,ITM.code 

UNION ALL

-- 再查询flag=true的小计行
SELECT 
    '小计' AS item,
    it.vatpercent AS vat,
    SUM(it.AQty) AS firstquant,
    SUM(it.bQty) AS secquant,
    NULL AS volume,
    itm.userdefnum01 AS [alc%],
    SUM(itm.userdefnum02*itm.USERDEFNUM01*it.aQty) AS alclit,
    SUM(12.25*itm.userdefnum02*itm.USERDEFNUM01*it.aQty) AS tax,
    itm.HEUSERDEFBOOL01 AS flag,
    1 AS is_subtotal
FROM centlines it
INNER JOIN items itm ON itm.code=it.itemcode
INNER JOIN commercialentries ce ON ce.dentid=it.dentid
WHERE itm.HEUSERDEFBOOL01 = 1
GROUP BY itm.userdefnum01,it.vatpercent,itm.HEUSERDEFBOOL01

ORDER BY flag DESC, [alc%], vat, is_subtotal

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