如何取出SQL中的第几条记录

[pyangjie]

如何取出SQL中的第几条记录

我的数据库里面有好多条记录(假如有100条),我现在有一个动态变量num,num是外部的可以变的值,


num的值是不确定的,如果num=50则要取出数据库里面第50位的那条记录,

如果num = 20,则要取出数据库里面第20位的那条记录.

请问下这样的sql应该怎么写..?

[winstonbonaparte]

用动态语句

declare @S int
declare @Str varchar(1000)
set @S = 20
set @Str = 'select top ' + cast(@S as varchar(2)) + ' * from 表 '
exec(@Str)

[xiequan2]

sql 2005
declare @S int
set @S = 20
select top(@s) * from tb

[winstonbonaparte]

噢，看错了。你只是要取第几条记录，我看成要取前几条记录了。

declare @S int
declare @Str varchar(1000)
set @S = 20
set @Str = 'select top 1 * from (select top ' + cast(@S as varchar(2)) + ' * from 表 )order by ID desc'
exec(@Str)

[JonasFeng]

sql2005直接支持TOP 后面的变量

SELECT TOP 1 * FROM (SELECT TOP @NUM * FROM LI ORDER BY COL) LO ORDER BY LO.COL DESC   

2000的话，要拼接SQL

DECLARE @SQL NVARCHAR(100)
SELECT @SQL = 'SELECT............'
EXEC @SQL 

[--小F--]

全都写好了 支持兄弟们的

[guguda2008]

create table Material --材料表
(
    Mid int identity primary key,
    Mname varchar(100) not null,  --材料名称
    pid int not null ,--材料分两级0:为一级材料 ,二级材料此值为 foreign key references Material(mid)

)

insert Material (Mname,Pid) select '钢铁',0
union all select '木材',0
union all select '8号铁丝',1
union all select '三角铁',1
union all select '四棱主钢',1
union all select '红松木方',2
union all select '果松板方',2
union all select '塑钢',0 

select * from 
(select ROW_NUMBER()Over(ORDER BY mid) as 'x',* from Material) as a
where a.x=5

--取出第5条

[--小F--]

取n到m行

1. 
select top m * from tablename where id not in (select top n id from tablename order by id asc/*|desc*/) 

2. 
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入到临时表 
set rowcount n   --只取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 identity_col between n and m  

6.SQL2005开始.可以使用row_number() over()生成行号
;with cte as
(
 select id0=row_number() over(order by id),* from tablename
)
select * from cte where id0 between n to m

[zzz1975]

[Quote=引用 7 楼 fredrickhu 的回复:]
SQL code取n到m行

1.
select top m * from tablename where id not in (select top n id from tablename order by id asc/*|desc*/)

2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入到临时表
set rowcount n --只取n条结果
select * from 表变量 order by columnname desc

3.
select top n * from
(select top m * from tablename order by columnname)…
[/Quote]

顶
不错的回复

[xingn]

[Quote=引用 4 楼 winstonbonaparte 的回复:]
噢，看错了。你只是要取第几条记录，我看成要取前几条记录了。

SQL codedeclare @S int
declare @Str varchar(1000)
set @S = 20
set @Str = 'select top 1 * from (select top ' + cast(@S as varchar(2)) + ' * from 表 )order by ID desc'
exec(@Str)
[/Quote]
up

[Tomzzu]

如果是SQL2005的话楼上已经有很多例子了
如果是SQL2000的话, 如果数据表中没有标识列, 只能用临时表或表变量的方法了

[bluefangxiao]

up

[babygehui]

select * from (select row_number() over( order by 主键名)num,* from tblname) a
where num=@num

[feixianxxx]

create proc kk
@num int 
as 
begin 
  declare c_name cursor
  for select * from tb where id=@num
  open c_name
  fetch absolute @num from c_name
  close c_name
  deallocate c_name
end
declare @num 
set @num=50
exec kk @num

[q450616144]

很简单！！

select top1 * from 表 where id not in (select top20 id from 表)