在我的 SQL Server 上,我得到了下表 - 简化了:
year month category value
2009 9 1 10
2009 9 2 20
2009 9 3 40
... ... ... ...
现在我想按年、月和类别 (1+2, 3, ...) 分组,以便我的结果如下所示:
year month category value
2009 9 1+2 30
2009 9 3 40
... ... ... ...
无法对表进行任何更改。此外,重要的是 SQL 运行速度快......
如何使这个 GROUP BY?我还没有找到任何有用的东西......
谢谢你的帮助...
最佳答案
with cte as (
select
year, month, value,
case
when category in (1, 2) then '1+2'
else cast(category as varchar(max))
end as category
from Table1
)
select
year, month, category, sum(value)
from cte
group by year, month, category
或者
select
t.year, t.month, isnull(c.category, t.category), sum(value)
from Table1 as t
left outer join (values
(1, '1+2'),
(2, '1+2')
) as c(id, category) on c.id = t.category
group by t.year, t.month, isnull(c.category, t.category)
sql fiddle demo
关于SQL GROUP BY 一列的不同值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18982316/