数据空值替换问题
现有表b，其中拿出两个部门的数据，如下：
dptiddptnameage1age2age3age4age5yearmonth
01010121狼牙0000020121
01010121狼牙00-10-120122
01010121狼牙00-10-120125
01010121狼牙0000020126
01010121狼牙00-10-120128
01010121狼牙0005520129
01010121狼牙10001201210
01010121狼牙00-1109201211
01010121狼牙00-2-1-3201212
010151新兵0120320121
010151新兵02-10120123
010151新兵02-10120124
010151新兵02-10120125
010151新兵02-10120126
010151新兵02-10120127
010151新兵02-10120128
010151新兵02-10120129
010151新兵02-101201210
想要达到这种效果：如果一条数据均为0，则由前面不为0的数据补充上。并且一个部门要有12个月份的数据。还有一种情况：没有1月份数据，在这里没有表示出来，希望能考虑到。
其具体实现结果如下：
01010121狼牙0000020121
01010121狼牙00-10-120122
01010121狼牙00-10-120123
01010121狼牙00-10-120124
01010121狼牙00-10-120125
01010121狼牙00-10-120126
01010121狼牙00-10-120127
01010121狼牙00-10-120128
01010121狼牙0005520129
01010121狼牙10001201210
01010121狼牙00-1109201211
01010121狼牙00-2-1-3201212
010151新兵0120320121
010151新兵0120320122
010151新兵02-10120123
010151新兵02-10120124
010151新兵02-10120125
010151新兵02-10120127
010151新兵02-10120128
010151新兵02-10120129
010151新兵02-101201210
010151新兵02-101201211
010151新兵02-101201212 空值替换 一年12条数据
[解决办法]
with tb(dptid, dptname,age1,age2,age3,age4,age5,[year],[month])
as(
select '01010121','狼牙',0,0,0,0,0,2012,1 union all
select '01010121','狼牙',0,0,-1,0,-1,2012,2 union all
select '01010121','狼牙',0,0,-1,0,-1,2012,5 union all
select '01010121','狼牙',0,0,0,0,0,2012,6 union all
select '01010121','狼牙',0,0,-1,0,-1,2012,8 union all
select '01010121','狼牙',0,0,0,5,5,2012,9 union all
select '01010121','狼牙',1,0,0,0,1,2012,10 union all
select '01010121','狼牙',0,0,-1,10,9,2012,11 union all
select '01010121','狼牙',0,0,-2,-1,-3,2012,12 union all
select '010151', '新兵',0,1,2,0,3,2012,1 union all
select '010151', '新兵',0,2,-1,0,1,2012,3 union all
select '010151', '新兵',0,2,-1,0,1,2012,4 union all
select '010151', '新兵',0,2,-1,0,1,2012,5 union all
select '010151', '新兵',0,2,-1,0,1,2012,6 union all
select '010151', '新兵',0,2,-1,0,1,2012,7 union all
select '010151', '新兵',0,2,-1,0,1,2012,8 union all
select '010151', '新兵',0,2,-1,0,1,2012,9 union all
select '010151', '新兵',0,2,-1,0,1,2012,10)
select tb1.* from tb tb1
union all
select tb1.dptid, tb1.dptname,tb1.age1,tb1.age2,tb1.age3,tb1.age4,tb1.age5,tb1.[year],c.number from tb tb1 , master..spt_values c where c.type='p' and c.number between 1 and 12
and (select count(1) from tb tb2 where tb2.dptid=tb1.dptid and tb2.dptname=tb1.dptname and tb2.[year]=tb1.[year] and tb2.[month]=c.number)<1
and (select max(tb2.[month]) from tb tb2 where tb2.dptid=tb1.dptid and tb2.dptname=tb1.dptname and tb2.[year]=tb1.[year] and tb2.[month]<c.number)=tb1.[month]
order by dptid,[year],[month]
