首页 > 代码库 > SQL分页

SQL分页

2024-08-09 12:41:28   216人阅读

有关分页SQL 的资料，最简单也比较好用的方法；复杂的系统就不要这样用了。


方法1：
适用于 SQL Server 2000/2005

SELECT TOP 页大小 *
FROM table1
WHERE id NOT IN
          (
          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
          )
ORDER BY id


方法2：
适用于 SQL Server 2000/2005

SELECT TOP 页大小 *
FROM table1
WHERE id >
          (
          SELECT ISNULL(MAX(id),0) 
          FROM 
                (
                SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
                ) A
          )
ORDER BY id


方法3：
适用于 SQL Server 2005

SELECT TOP 页大小 * 
FROM 
        (
        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
        ) A
WHERE RowNumber > 页大小*(页数-1)


说明，页大小：每页的行数；页数：第几页。使用时，请把“页大小”和“页大小*(页数-1)”替换成数字。

 

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页)   效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用

 

--备注：实际执行时SQL代码

--1
SELECT TOP 10 * FROM Sys_FunctionSet
WHERE AutoID NOT IN

--用NOT IN 会效率较低
(SELECT TOP 20 AutoID FROM Sys_FunctionSet ORDER BY AutoID)
ORDER BY AutoID

--2

--此种较好
SELECT TOP 10 *
FROM Sys_FunctionSet
WHERE AutoID >
(

SELECT ISNULL(MAX(AutoID),0) FROM (SELECT TOP 10 AutoID FROM Sys_FunctionSet ORDER BY AutoID) A
)
ORDER BY AutoID

--3
SELECT TOP 10 * FROM

--此处会查询出全部字段的内容，这种方法不好，效率太低
(SELECT ROW_NUMBER() OVER (ORDER BY AutoID) AS RowNumber,* FROM Sys_FunctionSet) A
WHERE RowNumber > 10

 

--以下为自己C#程序中的代码，用的和这个第二种一样：

代码较乱，以前的，未整理，

string orderbyFieldName = orderbyFields;
string SQL = " "; //产生临时表

string StoreType = " ";//顺序
string logic = " > ";
string cal = " Max ";
if (isAsc == true)
{
//顺序
StoreType = " ASC ";
logic = " > ";
cal = " Max ";
}
else
{
//倒序
StoreType = " Desc ";
logic = " < ";
cal = " Min ";
}

 

if (string.IsNullOrEmpty(orderbyFields) == false)
{
orderbyFields = " Order By " + orderbyFields;
}
else
{
StoreType = " ";
}
;

 


if (curpage < 2)
{

SQL = " Select Top " + pagesize.ToString() + " * From ( " + nowSQL + " ) as TmpPageSql ";
//SQL = SQL + orderbyFields + StoreType;
//if ((orderbyFieldName.Trim().ToUpper() != keyFields.ToUpper().Trim()) && (string.IsNullOrEmpty(keyFields.Trim()) == false) && (string.IsNullOrEmpty(orderbyFieldName.Trim()) == false))
//{
// SQL = SQL + orderbyFields + "," + keyFields + " " + StoreType;
//}
//else
//{
SQL = SQL + orderbyFields + " " + StoreType;
//}
}
else
{
//如果没有检测SQL版本就先检测SQL版本
if (isCheckVersion == false)
{
if (DataCenter.ExecuteScalar("select @@Version").ToString().IndexOf("2000") > -1)
{
isSQL2000 = true;
}
else
{
isSQL2000 = false;
}

isCheckVersion = true;//代表已经检测了服务器版本
}

if (isSQL2000 == true)
{

int topSize = this.PageSize;
try
{
topSize = pagesize * (curpage - 1);
}
catch
{

}

if (isSQL2000 == true)
{

int topSize = this.PageSize;
try
{
topSize = pagesize * (curpage - 1);
}
catch
{

}

#region SQL2000分页代码

SQL = SQL + "\n"
+ " Select Top " + pagesize.ToString() + " * from (" + nowSQL + ") as TmpPageSql \n "
+ " Where \n"
+ keyFields + logic + "\n"
+ " ("
+ " select " + cal + "(oceanTempKeyField) From "
+ " (Select Top " + topSize.ToString() + " " + keyFields + " as oceanTempKeyField "
+ " from (" + NowSqlString + ") as tmpold "
+ orderbyFields;

SQL = SQL + StoreType

+ " ) as tmptop "
+ " ) "
+ orderbyFields + StoreType;

#endregion
}}

SQL分页


联系
我们