sql语句
0
[已解决问题]
解决于 2012-06-13 17:18
从数据库中的一张表中取出N条记录，取出的记录数由用户输入，用户可以设定从表中的哪条记录开始取，要求取出的记录为F_Name字段的值等于 “alice”，表的主键由自增长字段组成，这个sql怎么写？
最佳答案
0
--取三条记录
declare @number int
set @number =3
--要取的名字
declare @F_Name nvarchar(50)
set @F_Name='alice'
--语句如下
select *
from
(
select top(@number) id,f_name
from
(
select top (
select @number+count(*)
from local
where id<(
select id
from local
where f_name=@f_name
)
) id,f_name
from local
order by id asc
)tmp
order by id desc
)t
order by id asc
在SQL2005中,还可以使用row_number()函数
declare @number int
set @number=3;
declare @f_name nvarchar(50)
set @f_name='alice';
with tmp as
(
select row_number() over(order by id asc) rownumber,id,f_name
from local
)
select id,f_name
from tmp
where rownumber between (
select count(*)
from local
where id<=(select id from local where f_name=@f_name)
)
and (
select count(*)+@number
from local
where id<(select id from local where f_name=@f_name)
)
奖励园豆：5
其他回答(1)
0
不要那么复杂吧！
select top @number ID,F_name from table
where ID>=@StartID and F_name ='alice'
