比如，我需要结果集中的第100到第150条数据！在2005中可以用rownumber()，不知道在2000中如何实现？
------解决方案--------------------
select identity(int,1,1) as rownumber * into # from 表名
select * from # where rownumber between 100 and 150
------解决方案--------------------
-- 例子 --
create table tab(c varchar(10),c1 varchar(10))
insert tab
select 'aa ', 'aa '
union select 'bb ', 'aa '
union select 'cc ', 'aa '
union select 'dd ', 'aa '
union select 'ee ', 'aa '
union select 'ff ', 'aa '
union select 'gg ', 'aa '
union select 'hh ', 'aa '
union select 'ii ', 'aa '
union select 'jj ', 'aa '
union select 'kk ', 'aa '
union select 'll ', 'aa '
union select 'mm ', 'aa '
select identity(int,1,1) as rownumber, * into # from tab
select * from # where rownumber between 3 and 5
drop table tab,#
------解决方案--------------------
取n到m条记录的语句
1.
select top m * from tablename where id not in (select top n * from tablename)
2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc
3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列，那么：
select identity(int) id0,* into #temp from tablename
取n到m条的语句为：
select * from #temp where id0 > =n and id0 <= m
如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
exec sp_dboption 你的DB名字, 'select into/bulkcopy ',true
5.如果表里有identity属性，那么简单：
select * from tablename where identitycol between n and m
------解决方案--------------------
--再给一个分组的
--按department分组取 第2 ~ 3条
--创建测试数据
declare @t table(idno int,name varchar(20),salary int,department varchar(50))
insert @t
select 1, '张一 ',100, '财务部 ' union all
select 2, '张二 ',200, '财务部 ' union all
select 3, '张三 ',300, '财务部 ' union all
select 4, '张四 ',400, '财务部 ' union all
select 5, '张五 ',100, '生产部 ' union all
select 6, '张六 ',200, '生产部 ' union all
select 7, '张七 ',300, '生产部 ' union all
select 8, '张八 ',400, '生产部 '
select * from
(
select * from @t x
where salary in
(select top 3 salary from @t y where y.department = x.department order by salary desc)
) z
where salary in
(select top 2 salary from
(
select * from @t x
where salary in
(select top 3 salary from @t y where y.department = x.department order by salary desc)
) p
where p.department = z.department order by salary)
--结果
/*
idno name salary department
------------------------------------------------
2 张二 200 财务部
3 张三 300 财务部
6 张六 200 生产部
7 张七 300 生产部
*/
------解决方案--------------------
假设id为主键:
select top 50 from tb where id not in(select top 100 id from tb)
------解决方案--------------------