<<<<<<<<<<求最优分页SQL语句<<<<<<<<<<<<<<<<<<<1000

Question

不知大家在写分页SQL时一般选择怎么写,大家都说一下,对于信息量大,多表关联(如10个表)的分页SQL语句,怎么写最优.如,每次只返回20条(pagesize=20),
要求:返回的内容(字段)可能是多张表中的字段,返回总记录数(recordcount)请认真考虑:信息量大,多表关联
求SQL,或思路.

Answer 1

this.sq_GetItemModel.Fill( DataSet , 但前纪录数 + 1 , 20 , TableName  );行不

Answer 2

看看我以前写的分页文章对你有没有帮助：）
http://www.cnblogs.com/upto/archive/2004/11/28/161653.html

Answer 3

SELECT * FROM (     SELECT TOP 20 a.id,a.a,b.b,c.c..     FROM (select a.id,a.a,b.b,c.c.. from a,b,c,d,e,f,g..where a.id=b.id and....)T1    WHERE T1.id< (       SELECT MIN(@PrimaryKey) FROM (         SELECT TOP (@RecordCount-@PageSize*(@PageIndex+1)) @PrimaryKey         FROM @TableName         WHERE @Condition         ORDER BY @PrimaryKey DESC         ) TableA       )WHERE @Condition       ORDER BY @PrimaryKey DESC     )TableB   ORDER BY @PrimaryKey ASC和
select top 20 from 
  (SELECT TOP PageSize*@PageIndex a.id,a.a,b.b,c.c From 
    (select a.id,a.a,b.b,c.c.. from a,b,c,d,e,f,g..where a.id=b.id and....)T1 
  order by T1.id Acs) T2 
obder by T2.id desc哪个好

Answer 4

个人收藏
最终版本，增加了倒排序功能，支持datetime类型CREATE PROCEDURE sp_page
  @tb         varchar(50), --表名
  @col        varchar(50), --按该列来进行分页
  @coltype    int,         --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
  @orderby    bit,         --排序,0-顺序,1-倒序
  @collist    varchar(800),--要查询出的字段列表,*表示全部字段
  @selecttype int,         --查询类型,1-前页,2-后页,3-首页,4-末页,5-指定页
  @pagesize   int,         --每页记录数
  @page       int,         --指定页
  @minid      varchar(50), --当前页最小号
  @maxid      varchar(50), --当前页最大号
  @condition  varchar(800) --查询条件
AS
/*
功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
         查询可以指定页大小、指定查询任意页、指定输出字段列表
作    者：pbsql
版    本：1.01
最后修改：2004-11-26
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
DECLARE @i int,@id varchar(50)
IF @coltype=1 or @coltype=2--字段类型为字符或日期时间要加上引号以作比较用
BEGIN
  SET @minid=''''+@minid+''''
  SET @maxid=''''+@maxid+''''
END
IF @condition is null or rtrim(@condition)=''--没有查询条件
BEGIN
  SET @where1=' WHERE '
  SET @where2='  '
END
ELSE--有查询条件
BEGIN
  SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
  SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql=
  CASE @selecttype
    WHEN 1--前页
    THEN 'SELECT * FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
         ' '+@collist+' FROM '+@tb+@where1+@col+
         CASE @orderby WHEN 0 THEN '<'+@minid ELSE '>'+@maxid END+
         ' ORDER BY '+@col+CASE @orderby WHEN 0 THEN ' DESC' ELSE '' END+
         ') t ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE 'DESC' END
    WHEN 2--后页
    THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
         ' FROM '+@tb+@where1+@col+
         CASE @orderby WHEN 0 THEN '>'+@maxid ELSE '<'+@minid END+
         ' ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
    WHEN 3--首页
    THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
         ' FROM '+@tb+@where2+'ORDER BY '+@col+
         CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
    WHEN 4--末页
    THEN 'SELECT * FROM (SELECT TOP '+CAST(@pagesize AS varchar)+' '+
         @collist+' FROM '+@tb+@where2+'ORDER BY '+@col+
         CASE @orderby WHEN 0 THEN ' DESC' ELSE '' END+') t ORDER BY '+
         @col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
  END
IF @selecttype>=1 and @selecttype<=4
BEGIN
  EXEC(@sql)
  RETURN
END
ELSE
BEGIN--指定页
  IF @coltype=1
    IF @orderby =0
      SET @id=''''''
    ELSE
      SET @id=''''+CHAR(255)+''''
  ELSE
    IF @coltype=2
      IF @orderby =0
        SET @id='''1753-1-1'''
      ELSE
        SET @id='''9999-12-31'''
    ELSE
      IF @orderby =0
        SET @id='-2147483648'
      ELSE
        SET @id='2147483647'
  SET @i=0
  --为减少之后SELECT TOP ...的数据量,此处每10000条循环一次,以尽可能接近所查询页
  WHILE @i<@pagesize*@page
  BEGIN
    IF @i+10000<@pagesize*@page
    BEGIN
      IF @orderby=0
        SET @sql='SELECT @id=CASE '+CAST(@coltype AS varchar)+
          ' WHEN 1 THEN ''''''''+CAST(MAX('+@col+') AS varchar(50))+'+
          ''''''''''+
          ' WHEN 2 THEN ''''''''+CONVERT(char(23),MAX('+@col+'),121)+'+
          ''''''''''+
          ' ELSE CAST(MAX('+@col+') AS varchar) END FROM (SELECT TOP 10000 '+
          @col+' FROM '+@tb+@where1+@col+'>'+@id+' ORDER BY '+@col+') t'
      ELSE
        SET @sql='SELECT @id=CASE '+CAST(@coltype AS varchar)+
          ' WHEN 1 THEN ''''''''+CAST(MIN('+@col+') AS varchar(50))+'+
          ''''''''''+
          ' WHEN 2 THEN ''''''''+CONVERT(char(23),MIN('+@col+'),121)+'+
          ''''''''''+
          ' ELSE CAST(MIN('+@col+') AS varchar) END FROM (SELECT TOP 10000 '+
          @col+' FROM '+@tb+@where1+@col+'<'+@id+' ORDER BY '+@col+' DESC) t'
      EXEC sp_executesql @sql,N'@id varchar(50) OUTPUT',@id OUTPUT
      SET @i=@i+10000
      IF @i+10000>=@pagesize*@page
        BREAK
    END
    ELSE
      BREAK
  END
  --上面的循环保证下面的子查询最多只有10000条数据
  IF @orderby=0
    SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
             ' FROM '+@tb+@where1+@col+'>'+@id+' AND '+@col+' NOT IN'+
             '(SELECT TOP '+CAST(@pagesize*(@page-1)-@i AS varchar)+
             ' '+@col+' FROM '+@tb+@where1+@col+'>'+@id+' ORDER BY '+@col+
             ') ORDER BY '+@col
  ELSE
    SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
             ' FROM '+@tb+@where1+@col+'<'+@id+' AND '+@col+' NOT IN'+
             '(SELECT TOP '+CAST(@pagesize*(@page-1)-@i AS varchar)+
             ' '+@col+' FROM '+@tb+@where1+@col+'<'+@id+' ORDER BY '+@col+
             ' DESC) ORDER BY '+@col+' DESC'
  EXEC(@sql)
END
GO
--测试事例
select identity(int,1,1) id,getdate() dt,xx=cast('' as varchar(10)) into #t
 from sysobjects
update #t set dt=dateadd(day,id-200,dt),
              xx='xxxx'+right('000000'+cast(id as varchar(10)),6)
--exec sp_page '#t','id',0,0,'*',5,10,3,'','',''--按id顺序取第三页
--exec sp_page '#t','id',0,1,'*',5,10,3,'','',''--按id倒序取第三页
--exec sp_page '#t','xx',1,0,'*',1,10,3,'xxxx000021','xxxx000030',''--按xx顺序取前一页
--exec sp_page '#t','xx',1,1,'*',2,10,3,'xxxx000134','xxxx000143',''--按xx倒序取后一页
--exec sp_page '#t','dt',2,0,'*',4,10,3,'','',''--按dt顺序取最后一页
exec sp_page '#t','dt',2,1,'*',3,10,3,'','',''--按dt倒序取首页
drop table #t

Answer 5

请认真考虑:信息量大,多表关联--这可要好好优化了,不然效率很成问题阿
sql server版有好多分页的存储过程
楼主可以去那边找找

Answer 6

以下两种分页从基本原理上可以达到目的,但请高手分析一下两者的性能问题,背景:多表关联,信息量大,>5W,查询返回的字段数为10左右.
------存储过程1-------
begin
select top @pagesize  t1.id,...  
from (select top @pagesize*@pageindex t1.id,... 
      from (select * 
            from t1 join t2 on ...join t3..on...join...
            where ...and..and.... 
            )t_1 
      order by t.id asc
     )t_0 
order by t0.id desc
end 
---存储过程2-------
begin
--*要先算出行数
select count(id) from (SELECT TOP @PageSize*@PageIndex t1_id 
                      FROM from t1 join t2 join...join t3..on...join...
                      where ...and..and.... )
if(..count(id)..)
SELECT TOP @PageSize t1.id,... 
FROM t1 join t2 on... join t3 on...join...
WHERE ...and..and.... ... 
And @t1_id > ( 
               SELECT MAX(t1_id) 
               FROM ( SELECT TOP @PageSize*@PageIndex t1_id 
                      FROM from t1 join t2 join...join t3..on...join...
                      where ...and..and.... 
                    )t1
               ORDER BY t1_id ASC 
              ) t0  ORDER BY t0_id ASC
end

Answer 7

转
CREATE PROCEDURE GetProductsPaged
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [standard search criteria]
AND ProductID > @lastProductID
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO
//或：
select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表
select * from #temp where ID_Num>10 and ID_Num<=20
或：
   SELECT Top @PageSize *
     FROM T
    WHERE SortField NOT IN (SELECT TOP @PageSize * @Pagei SortField
                              FROM T
                          ORDER BY SortField
                            )
 ORDER BY SortField