sql 分页问题

发布网友 发布时间：2022-09-20 16:42

我来回答

共3个回答

热心网友 时间：2023-11-03 16:30

http://www.builder.com.cn/2007/0912/501756.shtml
在绝大多数的应用中，返回大量的符合条件的记录是司空见惯的，最典型的莫过于搜索了。在搜索的应用中，用户给出搜索条件，服务器查出符合条件的记录。但往往搜索会返回大量的数据记录，如果在网页中，往往是通过分页的方式来实现，页面提供诸如上一页，下一页等等按钮来实现分页。

现在主要有以下几中方式来实现分页：

1.EJB的Find方法

2.在显示第一页前将所有的记录放在collection对象中，后续的各页都在内存中取。

3.每一页都到数据库作一次重复的查询，然后将符合条件的记录显示出来。

4.保存前几页的搜索结果在内存中（经常使用到的），后续的记录（这些数据是多数用户都没有耐心看的）再通过连数据库取得。

5.其他各种cache机制。

我们可以根据实际情况来选择适合自己的方法，我给大家介绍一个通过标准的SQL语句来得到符合条件的数据。如从第10到20条的符合条件（where语句中的）的记录。通过这种方法取得记录有一个必要条件，必须有一个能够标识记录顺序的字段，如id，time等等。下面我为大家演示一个例子：

查询t_table表中所有记录第10到20条，按id排序。

SQL语句为：

SELECT * FROM t_table t1 WHERE (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id ) > = 10 AND (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id ) < 20

又如查询t_table表中key='123'第10到20条的记录，按id排序。

SELECT * FROM t_table t1 WHERE (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id AND t2.key = '123') > = 10 AND (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id AND t2.key = '123') < 20 AND t1.key = '123'

当然条件中的id可以换为任何能标识记录顺序的字段。

http://www.builder.com.cn/2007/0912/501756.shtml

热心网友 时间：2023-11-03 16:31

drop table ItemData

create table [ItemData] (
[code] [char] (2) not null ,
[date] [datetime] not null ,
[iCode] [char] (11) not null ,
[itCode] [varchar] (20) not null ,
[cCode] [varchar] (10) not null ,
[pCode] [varchar] (10) not null ,
[fCode] [char] (2) null ,
[mvalue] [varchar] (50) null ,
[sys1] [varchar] (100) null ,
[sys2] [varchar] (100) null ,
-- constraint [pk_itemdata] primary key clustered
-- (
-- [code],
-- [date],
-- [iCode],
-- [itCode],
-- [cCode],
-- [pCode]
-- ) on [primary]
) --on [primary]
go

insert into ItemData(code,date,iCode,itCode,cCode,pCode)

select '03','2006-06-30 00:00:00.000','5','C1P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C2P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C3P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C4P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C5P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C6P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C7P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C8P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C9P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C10P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C11P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C12P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C13P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C14P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C15P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C16P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C17P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C18P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C19P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C20P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C21P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C22P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C24P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C25P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C26P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C27P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C29P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C30P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C31P','o','01'
union all
select '03','2006-06-30 00:00:00.000','5','C32P','o','01'

create proc page
(
@pageSize int,
@startIndex int,
@orderByString varchar(200)
)
as
declare @sql varchar(8000)
set @sql = ''
begin
select @sql = @sql + 'select top '
select @sql = @sql + ''+cast(@pageSize as varchar(10))+'' + ' * from (select * from ItemData) A where convert(varchar(10),A.date ,101)+A.iCode+A.itCode+A.cCode+A.pCode Not In '
select @sql = @sql + ' (select top '+cast(@startIndex as varchar(10))+' convert(varchar(10),date ,101)+iCode+itCode+cCode+pCode from ItemData)'
select @sql = @sql + ' order by '+@orderByString+''

end
exec (@sql)

exec page '10','10','date,iCode,itCode,cCode,pCode'

热心网友 时间：2023-11-03 16:31

SELECT TOP pagesize *FROM table---这句语法就是个错误

SELECT--查询，固定格式
TOP--是选出前几列
pagesize--是你表里字段名
*--这里看似是多余的，pagesize和*只可认选其一
FROM--从哪张表选，固定格式
table--表名

正常该这么写
SELECT TOP pagesize FROM table order by 某字段