times number
2006-1 5
2006-2 10
2006-5 8
2006-8 16
2006-12 9
表名是:asset
问题: 上表左列内容不全,1-12个月少了7个月,请用T-SQL语言实现,补全左列内容,补上的左列对应的右列值为0,
上表在数据库中不变.
求问题的代码....
------解决方案--------------------
- SQL code
--原始数据：#assetcreate table #asset(times varchar(7),number int)insert #assetselect '2006-1',5 union allselect '2006-2',10 union allselect '2006-5',8 union allselect '2006-8',16 union allselect '2006-12',9select top 12 mon=identity(int,1,1) into #mon from syscolumnsselect times='2006-'+ltrim(a.mon),number=isnull(b.number,0) from #mon a left join #asset b on '2006-'+ltrim(a.mon)=b.times/*times number ----------------- ----------- 2006-1 52006-2 102006-3 02006-4 02006-5 82006-6 02006-7 02006-8 162006-9 02006-10 02006-11 02006-12 9*/drop table #asset,#mon