declare @i int
set @i=1
while(@i<10000)
begin
insert into a select @i,left(newid(),7),12
set @i += 1
end
三、分页sql,下面例子是每页10条,取第31-40条数据。
--方法一:Order by
Select Top (40-31+1) * From a Where ID in (Select Top 40 ID From a Order by ID ) Order by ID Desc
–方法二:not in/top select top 10 * from a where id not in (select top 30 id from a order by id) order by id
–方法三:not exists select top 10 * from a where not exists (select 1 from (select top 30 id from a order by id)a1 where a1.id=a.id) order by id
–方法四:max/top select top 10 * from a where id>(select max(id) from (select top 30 id from a order by id)a1) order by id
–方法五:row_number() select top 10 from (select row_number()over(order by id)rownumber, from a)a1 where rownumber>30 select from (select row_number()over(order by id)rownumber, from a) a1 where rownumber>30 and rownumber<41 select from (select row_number()over(order by id)rownumber, from a)a1 where rownumber between 31 and 40
–方法六:row_number() 变体,不基于已有字段产生记录序号,先按条件筛选以及排好序,再在结果集上给一常量列用于产生记录序号 select from (select row_number()over(order by id)rownumber, from (select top 40 * from a where 1=1 order by id)a )b where rownumber>30