大数据量下数据分页的方式

时间:2022-02-18 11:30:48

SQL Server 2000

PageCount:一页需要的数据条数

PageIndex:页索引

 

方式1

select top PageCount * from (

select top PageCount * from (

select top PageCount*PageIndex  *  from tableName order by ID) as tmp1 order by ID DESC

) as tmp2 order by ID

)

方式2

select * from tableName where ID in (

     select top PageCount BillID from (

        select top PageCount*PageIndex ID from tableName order by ID

     ) temp1 order by ID DESC

) order by ID

 

Oracle 9i

SQL:普通的Select语句

FromIndex:从…条

ToIndex:到…条

通用的方式:

select * from (

     select row_.*, rownum rownum_ from (
       SQL

) row_ where rownum <= toIndex
) where rownum_ > fromIndex

 

有唯一标识符(ID字段)的情况排序

select * from tableName where ID in(
  select
ID
from (
    select rownum rownum_,
ID
from (
      select ID from
tableName order by Code

    ) where rownum <=
toIndex
  ) where rownum_ >
fromIndex
)