根据工资分组的问题

[fskjb01]

年度 工资 补贴
201001 1655 297.9
201002 1655 297.9
201003 1655 297.9
201004 1655 297.9
201005 1655 297.9
201006 1655 297.9
201007 1655 297.9
201008 1655 297.9
201009 1655 297.9
201010 1655 297.9
201011 1656 297.9
201012 1655 297.9

怎么写sql得到下面这个结果呢

年度开始结束 工资 补贴合计
201001~201010 1655 2979
201011 1656 297.9
201012 1655 297.9

[jmx123456789]

select 年度开始结束='201001~201010',工资 ,补贴=(补贴) from TB where 年度>201011 union all select 年度,工资 ,补贴 from TB where 年度<=201011

[哥眼神纯洁不]

with tb(a,b,c)as(
select '201001',1655,297.9 union
select '201002',1655,297.9 union
select '201003',1655,297.9 union
select '201004',1655,297.9 union
select '201005',1655,297.9 union
select '201006',1655,297.9 union
select '201007',1655,297.9 union
select '201008',1655,297.9 union
select '201009',1655,297.9 union
select '201010',1655,297.9 union
select '201011',1656,297.9 union
select '201012',1655,297.9)
,tc as (select * from tb where a in(
select a from tb a where 
exists(select 1 from tb where (convert(int,a.a)-1=a or convert(int,a)-a.a=1) and a.b!=b )
union all
select top 1 A from tb
union all
select top 1 a from tb order by a desc)
),td as(
select A,d=case when (select MIN(a) from tc where a.b=b and a.c=c and a.a<a )=
(select min(a) from tc where a.a<a) then (select MIN(a) from tc where a.b=b and a.c=c and a.a<a )
else null end,
b,c from tc a 
)
select a+isnull('~'+d,''),b,
c*isnull((DATEDIFF(month,convert(datetime,a+'01'),CONVERT(datetime,d+'01'))+1),1) 
from td
where a not in(select isnull(d,'') from td)

a,b,c分别对应三列，2008能通过...

[jmx123456789]

select 年度开始结束='201001~201010',工资=1655 ,补贴=(select sum(补贴) from TB where 年度>201011) union all select 年度,工资 ,补贴 from TB where 年度<=201011

[lzw_0736]

WITH a1 (ym,gz,bt)AS ( SELECT '201001',1655, 297.9 UNION all SELECT '201002',1655, 297.9 UNION all SELECT '201003',1655, 297.9 UNION all SELECT '201004',1655, 297.9 UNION all SELECT '201005',1655, 297.9 UNION all SELECT '201006',1655, 297.9 UNION all SELECT '201007',1655, 297.9 UNION all SELECT '201008',1655, 297.9 UNION all SELECT '201009',1655, 297.9 UNION all SELECT '201010',1655, 297.9 UNION all SELECT '201011',1656, 297.9 UNION all SELECT '201012',1655, 297.9 ) ,a2 AS ( SELECT *,ROW_NUMBER() OVER(ORDER BY GETDATE()) re FROM a1 ) ,a3 AS ( SELECT *,(SELECT gz FROM a2 WHERE re=a.re+1) gz2 FROM a2 a ) ,a4 AS ( SELECT * FROM a3 WHERE gz2 IS NULL OR gz2<>gz ) ,a5 AS ( SELECT *,(SELECT TOP 1 ym FROM a4 WHERE re>=a.re ORDER BY re asc) ym_end FROM a2 a ) ,b3 AS ( SELECT *,(SELECT gz FROM a2 WHERE re=a.re-1) gz2 FROM a2 a ) ,b4 AS ( SELECT * FROM b3 WHERE gz2 IS NULL OR gz2<>gz ) ,b5 AS ( SELECT *,(SELECT TOP 1 ym FROM b4 WHERE re<=a.re ORDER BY re desc) ym_start FROM a2 a ) ,c1 AS ( SELECT a.*,b.ym_start FROM a5 a INNER JOIN b5 b ON a.re=b.re ) ,c2 AS ( SELECT ym_start,ym_end,gz,SUM(bt) bt FROM c1 GROUP BY ym_start,ym_end,gz ) SELECT ym_start+CASE WHEN ym_start=ym_end THEN '' ELSE '~'+ym_end END ym,gz,bt FROM c2