SQL分页

1.利用Not In和SELECT TOP分页

SELECT TOP 10 * FROM   Table_A WHERE  Id   NOT   IN (SELECT   TOP   20    id   FROM  Table_A  ORDER BY id )) ORDER BY ID

 

2.利用ID大于多少和SELECT TOP分页

SELECT TOP 10 * FROM   Table_A  WHERE (ID   > (SELECT     MAX(id)    FROM (SELECT   TOP    20   id FROM    Table_A   ORDER BY id)  Table_alian))

 

3.利用SQL的游标存储过程分页

Oracle:
create or replace procedure getPage(
    pageSize integer, --每页记录数
    pageNo integer, --当前页码,从 1 开始
    sqlQuery varchar2, --查询语句,含排序部分
    rs out sys_refcursor, --返回的记录集
  rowCount out integer,  --返回总记录数
  pageCount out integer  --返回总页数
)
as
    sqlString varchar2(30000);
    startRowNum integer;
    endRowNum integer;
    pageNumber integer;
    begin

    ----取记录总数
 sqlString := 'select count(*) from (' || sqlQuery || ')';
 execute immediate sqlString into rowCount;

 if pageNo > 0 then
     ----计算
     pageNumber := pageNo;

     pageCount := ceil(1.0 * rowCount / PageSize);
     if pageNumber > pageCount then
         pageNumber := pageCount;
    -- else
  --       pageNumber := ( pageCount - 1 )  ;
     end if;

     ----执行分页查询
  --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
     startRowNum := pageNumber * pageSize;
     endRowNum := startRowNum - pageSize + 1;
     sqlString := 'SELECT *  FROM (
     SELECT A.*, rownum rn
     FROM ('|| sqlQuery ||') A
     WHERE rownum <= '|| to_char(startRowNum) || ' ) B
     WHERE rn >= ' || to_char(endRowNum) ;

     --dbms_output.put_line(sqlString);
 else
     sqlString := 'SELECT *  FROM ( '|| sqlQuery ||') A';

 end if;

 OPEN rs FOR sqlString;
 --dbms_output.put_line(sqlString);
 --dbms_output.put_line(rowCount);
 --dbms_output.put_line(pageCount);

end getPage;

SQL SERVER:

CREATE PROCEDURE [dbo].[GetDataRecordFromPage]
 @SelectList VARCHAR(2000),         --欲选择字段列表
 @TableSource VARCHAR(100),         --表名或视图表
 @SearchCondition VARCHAR(2000),    --查询条件
 @OrderExpression VARCHAR(1000),    --排序表达式
 @PageIndex INT = 1,                --页号,从0开始
 @PageSize INT = 10                 --页尺寸
AS
BEGIN
 IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''
 BEGIN
 SET @SelectList = '*'
 END
 PRINT @SelectList
 
 SET @SearchCondition = ISNULL(@SearchCondition,'')
 SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))
 IF @SearchCondition <> ''
 BEGIN
 IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'
 BEGIN
 SET @SearchCondition = 'WHERE ' + @SearchCondition
 END
 END
 PRINT @SearchCondition

 SET @OrderExpression = ISNULL(@OrderExpression,'')
 SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))
 IF @OrderExpression <> ''
 BEGIN
 IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'WHERE'
 BEGIN
 SET @OrderExpression = 'ORDER BY ' + @OrderExpression
 END
 END
 PRINT @OrderExpression

 IF @PageIndex IS NULL OR @PageIndex < 1
 BEGIN
 SET @PageIndex = 1
 END
 PRINT @PageIndex
 IF @PageSize IS NULL OR @PageSize < 1
 BEGIN
 SET @PageSize = 10
 END
 PRINT @PageSize

 DECLARE @SqlQuery VARCHAR(4000)

 SET @SqlQuery='SELECT '+@SelectList+',RowNumber
 FROM
 (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber
 FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource
 WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR)
 + ' AND ' +
 CAST((@PageIndex * @PageSize) AS VARCHAR)
-- ORDER BY ' + @OrderExpression
 PRINT @SqlQuery
 SET NOCOUNT ON
 EXECUTE(@SqlQuery)
 SET NOCOUNT OFF
 
 RETURN @@RowCount
END

 

 

 

4.利用left join

select top 10 * from Table_A left join (select top 10 id from Table_A ) Table_B  where Table_B.id is null;

 

5.利用order by,不过这种速度不行

select top 10 * from (select * from （select top 20 * from Table_A order by id asc) Table_B order by Table_B desc) Table_C order by Table_C.id;

 

6.如果确定分页只有一两页，不会太多，还不如直接读取出来在程序里边自己分页。