updat t set t1 = 'g' Where t1 is not null updat t set t2 = 'g' Where t2 is not null updat t set t3 = 'g' Where t3 is not null updat t set t4 = 'g' Where t4 is not null
declare @colcount int,@i int,@colname varchar(50) select @colcount=count(*) from syscolumns where id = OBJECT_ID('TABLE1') and number = 0 set @i=1 while (@i<=@colcount) begin select @colname =COL_NAME(OBJECT_ID('TABLE1'), @i) update TABLE1 set @colname = 'g' Where @colname is not null set @i=@i+1 end
这样写更好些 declare @colcount int,@i int,@colname varchar(50),@sql varchar(500) select @colcount=count(*) from syscolumns where id = OBJECT_ID('TABLE1') and number = 0 set @i=1 while (@i<=@colcount) begin select @colname =COL_NAME(OBJECT_ID('TABLE1'), @i) set @sql='update TABLE1 set '+convert(varchar(50),@colname)+' = ''g'' Where '+convert(varchar(50),@colname)+' is not null' exec (@sql) set @i=@i+1 end
update table1 set table1.y1 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='1' update table1 set table1.y2 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='2' update table1 set table1.y3 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='3' update table1 set table1.y4 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='4' update table1 set table1.y5 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='5' update table1 set table1.y6 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='6' update table1 set table1.y7 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='7' ..... 请问如何改循环简单点啊
update table1 set table1.y1 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='1' update table1 set table1.y2 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='2' update table1 set table1.y3 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='3' update table1 set table1.y4 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='4' update table1 set table1.y5 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='5' update table1 set table1.y6 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='6' update table1 set table1.y7 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='7' update table1 set table1.y8 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='8' update table1 set table1.y9 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='9' update table1 set table1.y10 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='10' update table1 set table1.y11 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='11' update table1 set table1.y12 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='12' 请问如何用存储过程改循环简单点啊
declare @colcount int,@i int,@sql varchar(500) set @i=1 while (@i<=12) begin set @sql='update table1 set table1.y'+convert(varchar(2),@i)+' = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='''+convert(varchar(2),@i)+'''' set @i=@i+1 select @sql end
updat t set t2 = 'g' Where t2 is not null
updat t set t3 = 'g' Where t3 is not null
updat t set t4 = 'g' Where t4 is not null
select @colcount=count(*) from syscolumns where id = OBJECT_ID('TABLE1') and number = 0
set @i=1
while (@i<=@colcount)
begin
select @colname =COL_NAME(OBJECT_ID('TABLE1'), @i)
update TABLE1 set @colname = 'g' Where @colname is not null
set @i=@i+1
end
declare @colcount int,@i int,@colname varchar(50),@sql varchar(500)
select @colcount=count(*) from syscolumns where id = OBJECT_ID('TABLE1') and number = 0
set @i=1
while (@i<=@colcount)
begin
select @colname =COL_NAME(OBJECT_ID('TABLE1'), @i)
set @sql='update TABLE1 set '+convert(varchar(50),@colname)+' = ''g'' Where '+convert(varchar(50),@colname)+' is not null'
exec (@sql)
set @i=@i+1
end
update table1 set table1.y2 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='2'
update table1 set table1.y3 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='3'
update table1 set table1.y4 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='4'
update table1 set table1.y5 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='5'
update table1 set table1.y6 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='6'
update table1 set table1.y7 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='7'
.....
请问如何改循环简单点啊
update table1 set table1.y2 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='2'
update table1 set table1.y3 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='3'
update table1 set table1.y4 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='4'
update table1 set table1.y5 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='5'
update table1 set table1.y6 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='6'
update table1 set table1.y7 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='7'
update table1 set table1.y8 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='8'
update table1 set table1.y9 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='9'
update table1 set table1.y10 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='10'
update table1 set table1.y11 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='11'
update table1 set table1.y12 = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='12'
请问如何用存储过程改循环简单点啊
set @i=1
while (@i<=12)
begin
set @sql='update table1 set table1.y'+convert(varchar(2),@i)+' = table2.特征值 from table1, table2 where table1.n1=table2.t1 and table1.n2=table2.t2 and table1.n3=table2.t3 and month(时间)='''+convert(varchar(2),@i)+''''
set @i=@i+1
select @sql
end