sql Server分组查询

CREATE TABLE test
(
 id int primary key not null identity,
 names varchar(20)
);

declare @i int;
set @i=1;
while(@i<1000)
begin
    insert into test values(‘t‘+cast(@i as varchar(8)));
    select @i=@i+1;
end
--方法1
select top 10 *
from test
where id not in
(
 --40是这么计算出来的：10*(5-1)
 select top 40 id from test order by id
)
order by id
--方法2
select top 10 *
from test
where id >
(
 select max(id)
 from 
  (
   select top 40 id from test order by id
  ) A
)
order by id;

--方法3
select top 10 *
from 
(
 select row_number() over(order by id) as rownumber,* from test
) A
where rownumber > 40
--方法4
select *
from 
(
 select row_number() over(order by id) as rownumber,* from test
) A
where rownumber >= 41 and rownumber <= 50