//选择前10条记录 select top 10 * from Table //进一步 选择10到20条记录 SELECT TOP 10 * FROM (SELECT TOP 20 * FROM Comment WHERE IsDel = 0 ORDER BY CommentID ASC) as a ORDER BY CommentID DESC//先按照顺序取前20条记录,然后在选择的记录基础上倒序取前10条,正好是10-20条记录,273-300的用法同理.
SELECT TOP 10 * FROM Info WHERE (id BETWEEN 起始ID AND 终止ID)
CREATE PROCEDURE dbo.spAll_ReturnRows ( @SQL nVARCHAR(4000), --SQL语句 @Page int, --显示第几页 @RecsPerPage int, --每页几条记录 @ID VARCHAR(255), --唯一键字段名 @Sort VARCHAR(255) --排序字段名 ) ASDECLARE @Str nVARCHAR(4000)SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN (SELECT?? TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@SortPRINT @StrEXEC sp_ExecuteSql @Str GO测试可用
CREATE procedure zs_GetList(@pageno int,@pagesize int ,@userid varchar(11)) as declare @startid int declare @endid intset @startid=(@pageno-1)* @pagesize+1 set @endid=@pageno * @pagesize begin select IDENTITY(int,1,1) as id,content,toid into #temp from liuyan where toid=@Userid order by lytime desc select * from #temp where id between @startid and @endid drop table #tempend 我用的分页存储过程
select top 3 * from (select top 6 * from table where id not in (select top 3 id from table )) ;
select top 10 * from Table
//进一步 选择10到20条记录
SELECT TOP 10 *
FROM (SELECT TOP 20 *
FROM Comment
WHERE IsDel = 0
ORDER BY CommentID ASC) as a
ORDER BY CommentID DESC//先按照顺序取前20条记录,然后在选择的记录基础上倒序取前10条,正好是10-20条记录,273-300的用法同理.
FROM Info
WHERE (id BETWEEN 起始ID AND 终止ID)
CREATE PROCEDURE dbo.spAll_ReturnRows
(
@SQL nVARCHAR(4000), --SQL语句
@Page int, --显示第几页
@RecsPerPage int, --每页几条记录
@ID VARCHAR(255), --唯一键字段名
@Sort VARCHAR(255) --排序字段名
)
ASDECLARE @Str nVARCHAR(4000)SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN
(SELECT?? TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@SortPRINT @StrEXEC sp_ExecuteSql @Str
GO测试可用
as
declare @startid int
declare @endid intset @startid=(@pageno-1)* @pagesize+1
set @endid=@pageno * @pagesize
begin
select IDENTITY(int,1,1) as id,content,toid into #temp from liuyan where toid=@Userid order by lytime desc
select * from #temp where id between @startid and @endid
drop table #tempend
我用的分页存储过程
from
(select top 6 *
from table
where id not in (select top 3 id from table )) ;
指定读取记录写查询语句条件 就用 between...and...条件