SQL查询按门店汇总金额结果拆分 如何实现门店及总计正确汇总

问题根因 #

• 外层查询GROUP BY子句错误添加了g.BrutPrice、g.NetPrice字段，导致每一个不同的单价都会单独分组，无法实现按门店的全局聚合
• 外层SELECT的DISTINCT是多余语法，GROUP BY按门店分组后结果天然去重
• 缺少总计行的生成逻辑

修改后的查询语句 #

SELECT 
    ISNULL(g.FacilityName, 'Grand Total') AS FacilityName,
    SUM(g.BrutPrice) AS AmountWithoutDiscount,
    SUM(g.NetPrice) AS DiscountedAmount
From(
SELECT e.FacilityName, b.ItemAmount, b.BrutPrice, b.NetPrice,
(SELECT DISTINCT f.ProductId FROM FAZIKI.dbo.NM_ProductCampaign f WHERE c.Id = f.ProductId AND (f.IsActive=1) AND (f.CampaignId='7') AND (f.ProductId IS NOT NULL)) as Product
FROM FAZIKI.dbo.PM_InvoiceInformation a
INNER JOIN FAZIKI.dbo.PM_InvoiceDetail b ON a.Id = b.InvoiceId 
INNER JOIN FAZIKI.dbo.PM_Product c ON b.ProductId = c.Id 
INNER JOIN FAZIKI.dbo.DF_GNFacility e ON a.FacilityId = e.Id
WHERE(a.InvoiceDate > CONVERT(DATETIME, '2021-11-08 00:00:00', 102)) AND (a.InvoiceType = 2) AND (a.IsFromNCR = 1) 
AND (c.RayonId = 1) AND (b.BrutPrice <> b.NetPrice) AND (a.IsCancel = 0)) AS g
WHERE g.Product IS NOT NULL 
GROUP BY g.FacilityName WITH ROLLUP

修改说明 #

• 删除了外层SELECT的多余DISTINCT关键字
• 简化GROUP BY子句，仅保留g.FacilityName作为分组维度，实现按门店聚合所有金额
• 添加WITH ROLLUP语法生成总计行，用ISNULL函数把ROLLUP生成的空门店名称替换为Grand Total，符合预期输出格式
• 若使用的数据库版本支持标准ROLLUP写法，也可以把GROUP BY g.FacilityName WITH ROLLUP替换为GROUP BY ROLLUP(g.FacilityName)，效果完全一致

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