程序如下,思路是设2层循环,内层循环用insert产生一列(0,15,30,45,60。、、、)存入#tb
外层循环一边建立新列,一边把#tb一行行复制入最终的表格中,最后应该产生n列0,15,30,45,60……为什么不行呢?
其实这是把刚才发过的问题简化了一些declare @distance decimal(6,3), @times int, @tongji int, @sql varchar(500), @number int, @sql2 varchar(500)
set @distance=0
set @times=0
set @number=1drop table results
create table results
(
resultID int IDENTITY(1,1),
a1 int
)WHILE (@distance<=20)
BEGIN
create table #tb
(
tID int IDENTITY(1,1),
a int
)
WHILE (@times<=1440)
Begin
insert into #tb (a) values (@times)
select @times=@times+15
End
select @distance=@distance+0.2
select @sql2= 'UPDATE results SET a'+convert(varchar(10),@number)+'= #tb.a FROM results, #tb WHERE results.resultID=#tb.tID'
exec(@sql2)
select @number=@number+1
select @sql='ALTER TABLE results ADD COLUMN a'+convert(varchar(10),@number)+' int' --自动加一列
exec(@sql)
drop table #tb
END
select * from results
外层循环一边建立新列,一边把#tb一行行复制入最终的表格中,最后应该产生n列0,15,30,45,60……为什么不行呢?
其实这是把刚才发过的问题简化了一些declare @distance decimal(6,3), @times int, @tongji int, @sql varchar(500), @number int, @sql2 varchar(500)
set @distance=0
set @times=0
set @number=1drop table results
create table results
(
resultID int IDENTITY(1,1),
a1 int
)WHILE (@distance<=20)
BEGIN
create table #tb
(
tID int IDENTITY(1,1),
a int
)
WHILE (@times<=1440)
Begin
insert into #tb (a) values (@times)
select @times=@times+15
End
select @distance=@distance+0.2
select @sql2= 'UPDATE results SET a'+convert(varchar(10),@number)+'= #tb.a FROM results, #tb WHERE results.resultID=#tb.tID'
exec(@sql2)
select @number=@number+1
select @sql='ALTER TABLE results ADD COLUMN a'+convert(varchar(10),@number)+' int' --自动加一列
exec(@sql)
drop table #tb
END
select * from results
insert的语句,只有update
怎么可能有记录呢
--〉
select @sql2= 'UPDATE results SET a'+convert(varchar(10),@number)+'= #tb.a FROM results, #tb WHERE results.resultID=#tb.tID
insert results(a'+convert(varchar(10),@number)+') select a from #tb where not exists ( seelct 1 from results WHERE results.resultID=#tb.tID )'ps:没测试,回家了,懒得测试了
set @distance=0
set @times=0
set @number=1drop table resultscreate table results
(
resultID int IDENTITY(1,1),
a1 int
)
insert into results select top 100 1 from sysobjects a,sysobjects b
WHILE (@distance<=10)
BEGIN
create table #tb
(
tID int IDENTITY(1,1),
a int
)
set @times=0
WHILE (@times<=1440)
Begin
insert into #tb (a) values (@times)
select @times=@times+15
End
select @distance=@distance+0.2
select @sql2= 'UPDATE results SET a'+convert(varchar(10),@number)+' = #tb.a FROM results, #tb WHERE results.resultID
=#tb.tID'
exec(@sql2)
select @number=@number+1
select @sql='ALTER TABLE results ADD a'+convert(varchar(10),@number)+' int' --自动加一列
exec(@sql)
drop table #tb
END
select * from results
能问一下top 100 1 from sysobjects a,sysobjects b 这个什么意思吗?