求助大神！在分类统计的最后添加总计

[weixin_39472492]

希望下面的分类统计的最后加上一行总计，代码该怎么写呢？同时还不能影响排序。先谢谢各位大神

SELECT
年度,部门,
SUM(CASE WHEN 产品 = 'A' THEN 销售量 ELSE 0 END) 产品A,
SUM(CASE WHEN 产品 = 'B' THEN 销售量 ELSE 0 END) 产品B,
SUM(CASE WHEN 产品 = 'C' THEN 销售量 ELSE 0 END) 产品C
FROM
销售信息
GROUP BY
年度
ORDER BY
年度 DESC

[吉普赛的歌]

;WITH cte as (
	SELECT
		LTRIM(年度) AS [年度]
		,部门,
		SUM(CASE WHEN 产品 = 'A' THEN 销售量 ELSE 0 END) 产品A,
		SUM(CASE WHEN 产品 = 'B' THEN 销售量 ELSE 0 END) 产品B,
		SUM(CASE WHEN 产品 = 'C' THEN 销售量 ELSE 0 END) 产品C
	FROM
		销售信息 
	GROUP BY
		年度
)
, cte2 AS (
	SELECT '9999' AS [年度]
		,'' AS [部门]
		,SUM(产品A) AS [产品A]
		,SUM(产品B) AS [产品B]
		,SUM(产品C) AS [产品C] FROM cte	
)
SELECT  
CASE WHEN [年度]='9999' THEN '合计' ELSE [年度] AS [年度]
,[部门]
,[产品A]
,[产品B]
,[产品C]
FROM (
	SELECT * FROM cte
	UNION ALL
	SELECT * FROM cte2
) AS t
ORDER BY
    年度 DESC

[吉普赛的歌]

引用 2 楼 weixin_39472492 的回复:

谢谢大神！@吉普赛的歌

不客气， 没事就结贴吧

[weixin_39472492]

谢谢大神！@吉普赛的歌

[weixin_39472492]

但是排序不是我期望的结果，ORDER BY 似乎 与 UNION ALL 有冲突

[吉普赛的歌]

;WITH cte as (
	SELECT
		LTRIM(年度) AS [年度]
		,部门,
		SUM(CASE WHEN 产品 = 'A' THEN 销售量 ELSE 0 END) 产品A,
		SUM(CASE WHEN 产品 = 'B' THEN 销售量 ELSE 0 END) 产品B,
		SUM(CASE WHEN 产品 = 'C' THEN 销售量 ELSE 0 END) 产品C
	FROM
		销售信息 
	GROUP BY
		年度
)
, cte2 AS (
	SELECT '9999' AS [年度]
		,'' AS [部门]
		,SUM(产品A) AS [产品A]
		,SUM(产品B) AS [产品B]
		,SUM(产品C) AS [产品C] FROM cte	
)
SELECT  
CASE WHEN [年度]='9999' THEN '合计' ELSE [年度] AS [年度]
,[部门]
,[产品A]
,[产品B]
,[产品C]
FROM (
	SELECT * FROM cte
	UNION ALL
	SELECT * FROM cte2
) AS t
ORDER BY
    CASE WHEN 年度='9999' THEN 1 ELSE 0 END ASC
	,[年度] DESC

如果还有问题， 那你贴就截图出来看看吧

[mingqing6364]

SELECT
    年度,部门,
    SUM(CASE WHEN 产品 = 'A' THEN 销售量 ELSE 0 END) 产品A,
    SUM(CASE WHEN 产品 = 'B' THEN 销售量 ELSE 0 END) 产品B,
    SUM(CASE WHEN 产品 = 'C' THEN 销售量 ELSE 0 END) 产品C
FROM
    销售信息 
GROUP BY
    年度, 部门
WITH ROLLUP
ORDER BY
    年度 DESC

试试with rollup关键字
参考资料：https://www.cnblogs.com/Mr-Cxy/p/5923375.html