times number
2006-1 5
2006-2 10
2006-5 8
2006-8 16
2006-12 9
表名是:asset
问题: 上表左列内容不全,1-12个月少了7个月,请用T-SQL语言实现,补全左列内容,补上的左列对应的右列值为0,
上表在数据库中不变.
求问题的代码....
2006-1 5
2006-2 10
2006-5 8
2006-8 16
2006-12 9
表名是:asset
问题: 上表左列内容不全,1-12个月少了7个月,请用T-SQL语言实现,补全左列内容,补上的左列对应的右列值为0,
上表在数据库中不变.
求问题的代码....
create table #asset(times varchar(7),number int)
insert #asset
select '2006-1',5 union all
select '2006-2',10 union all
select '2006-5',8 union all
select '2006-8',16 union all
select '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 5
2006-2 10
2006-3 0
2006-4 0
2006-5 8
2006-6 0
2006-7 0
2006-8 16
2006-9 0
2006-10 0
2006-11 0
2006-12 9
*/drop table #asset,#mon
insert into asseta
select '2006-1',0
union all
select '2006-2',0
union all
select '2006-3',0
union all
select '2006-4',0
union all
select '2006-5',0
union all
select '2006-6',0
union all
select '2006-7',0
union all
select '2006-8',0
union all
select '2006-9',0
union all
select '2006-10',0
union all
select '2006-11',0
union all
select'2006-12',0update a set number=b.number from asseta a join asset b on a.times=b.timesdrop table asset
sp_rename 'asseta','asset'
declare @i int ,@a varchar(10),
set @i=0
while @i<12
begin
set @i=@i+1
set @a='2006-'+cast(@i as varchar(2))
if not exists (select 1 from asseta where times=@a)
insert asseta
select @a,0
end