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

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
)