有关JSP分页的SQL语句！！

有关JSP分页的SQL语句！！

那只好先算出来记录序号了。然后写子查询出来，SQL的TOP关键字可以实现，但是参数不支持表达式，只能是常量。
SELECt *
FROM
(
　　SELECt TOP 5 *
　　FROM
　　(
　　　　SELECt TOP 15 *
　　　　FROM bean
　　　　ORDER BY ID ASE
　　) t
　　ORDER BY ID DESC
) t
ORDER BY ID ASE
还有一种写法，适合条件少的情况：
SELECt TOP 5 *
FROM bean
WHERe ID NOT IN
(
　　SELECt TOP 10 *
　　FROM bean
　　ORDER BY ID
)
ORDER BY ID

SELECt TOP 5 FROM bean WHERe ID NOT IN(SELECt TOP (5*(pageNo-1)) FROM bean ORDER BY ID DESC)ORDER BY ID DESC

alter proc porcpagination
@tablename varchar(20) ,--表名
@keyname varchar(50)='',--主键
@pagesize int =10,--每页多少行
@pageindex int=1 ,--现在是第几页
@columnnames varchar(200)='*',--显示那些列
@orderType bit=0 ,--排序规则
@wherestr varchar (500)='' ,--条件
@pagecount bit =0--返回行数
as
declare @sql varchar(1000)
declare @orderstr varchar(50)
declare @maxorminstr varchar (50)
if @pagecount<>0
begin
if @wherestr<>''
set @sql='select count(*) as pagecount from '+@tablename +' where '+@wherestr
else
set @sql='select count(*) as pagecount from '+@tablename
end
else
begin
if @columnnames=''
set @columnnames='*'
if @orderType<>0
begin
set @orderstr=' order by '+ @keyname +' desc '
set @maxorminstr ='>=(select min('+@keyname+') '
end
else
begin
set @orderstr=' order by '+ @keyname
set @maxorminstr ='>=(select max('+@keyname+') '
end
if @wherestr<>''
begin
set @sql='select top '+convert(varchar(50),@pagesize)+
' '+@columnnames +' from '+@tablename +' where '+@keyname+' '
+@maxorminstr+' from (select top '+
convert(varchar(50),(@pageindex-1)*@pagesize)+@columnnames +
' from '+@tablename +@orderstr+') as tmptable) and'+ @wherestr +@orderstr
end
else
begin
set @sql='select top '+convert(varchar(50),@pagesize)+
' '+@columnnames +' from '+@tablename +' where '+@keyname+' '
+@maxorminstr+' from (select top '+
convert(varchar(50),(@pageindex-1)*@pagesize)+@columnnames +
' from '+@tablename +@orderstr+') as tmptable)'+@orderstr
end
exec (@sql)
--print @sql
end
exec porcpagination 'jobs','min_lvl' ,2,3,'',1--调用

用存储过程比较好,不要怕麻烦,你编写起来是麻烦,但是用起来要比拼接SQL语句好用多了
示例代码:
USE [MyBookShop]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Get_Book_ByCategoryId]
(
@CategoryId uniqueidentifier,
@page int,
@row int
)
AS

declare @sql varchar(400)
set @sql='SELECt top '+convert(varchar(20),@row)+' Id,Title,Author,PublishDate,ISBN,WordsCount,UnitPrice,ContentDescription,AuthorDescription,EditorComment,Toc,Clicks,CategoryId,PublisherId,AdminName,BuyCount FROM Books where Id not in (select top '+cast((@row*(@page-1)) as varchar(4))+' id from books where CategoryId='''+cast(@CategoryId as varchar(36))+''' order by Title) and CategoryId='''+cast(@CategoryId as varchar(36))+''' order by Title'
exec (@sql)
RETURN