根据工资分组的问题
年度工资补贴
2010011655297.9
2010021655297.9
2010031655297.9
2010041655297.9
2010051655297.9
2010061655297.9
2010071655297.9
2010081655297.9
2010091655297.9
2010101655297.9
2010111656297.9
2010121655297.9
怎么写sql得到下面这个结果呢
年度开始结束工资补贴合计
201001~20101016552979
2010111656297.9
2010121655297.9
[解决办法]
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)