各位大神们请展示下你们生成数字序列1到100的方法。
先放一个：
with t
as
(select 1 as dt
union all
select dt+1 from t
where dt+1<=100)
select dt from t option(maxrecursion 0)
------解决方案--------------------
- SQL code
declare @i int set @i=1 xxoo: print @i set @[email protected]+1 if @i<101 goto xxoo
------解决方案--------------------
select number from master..spt_values where type='p' and number between 1 and 100
------解决方案--------------------
- SQL code
select number from master..spt_values b where b.type='p' and number between 1 and 100/*number ----------- 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100（所影响的行数为 100 行）*/
------解决方案--------------------
- SQL code
select top 100000 row_number()over(order by rand()) from syscolumns,sysobjects,sysindexes
------解决方案--------------------
- SQL code
--生成1-100的6种方法：--1.while(最简单的，最低效的方式)declare @t table (id int)declare @i int set @i=1while @i<101begin insert into @t select @i set @[email protected]+1endselect * from @t--2.if+goto（小爱已经给出了）declare @t1 table (id int)declare @j int set @j=1maco:insert into @t1 select @jset @[email protected]+1if(@j<101)goto macoselect * from @t1--3.identity+临时表select top 100 identity(int,1,1) id into #tmp from syscolumns,sysobjectsselect id from #tmp;drop table #tmp--4.row_number()(Vidor已经给出了)select top 100 row_number() over(order by (select 1)) from syscolumns,sysobjects--5.系统辅助表master..spt_values（SQL777和dawugui已经给出了）select number from master..spt_values where type='p' and number between 1 and 100--6.CTE递归（楼主已经给出了）;with t as(select 1 id union all select id+1 from t where id<100)select * from t --楼下继续补充
------解决方案--------------------
- SQL code
gocreate table tmp(ID int identity)goinsert tmp default valuesgo 100goselect * from tmpdrop table tmp