34,576
社区成员
发帖
与我相关
我的任务
分享
;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
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