C#拼接SQL语句,SQL Server 2005+,多行多列大数据量情况下,使用ROW_NUMBER实现的高效分页排序

时间:2021-07-02 23:20:51

/// <summary>
/// 单表(视图)获取分页SQL语句
/// </summary>
/// <param name="tableName">表名或视图名</param>
/// <param name="key">唯一键</param>
/// <param name="fields">获取的字段</param>
/// <param name="condition">查询条件(不包含WHERE)</param>
/// <param name="collatingSequence">排序规则(不包含ORDER BY)</param>
/// <param name="pageSize">页大小</param>
/// <param name="pageIndex">页码(从1开始)</param>
/// <returns>分页SQL语句</returns>
public static string GetPagingSQL(
string tableName,
string key,
string fields,
string condition,
string collatingSequence,
int pageSize,
int pageIndex)
{
string whereClause = string.Empty;
if (!string.IsNullOrEmpty(condition))
{
whereClause = string.Format("WHERE {0}", condition);
}

if (string.IsNullOrEmpty(collatingSequence))
{
collatingSequence = string.Format("{0} ASC", key);
}

StringBuilder sbSql = new StringBuilder();

sbSql.AppendFormat("SELECT {0} ", PrependTableName(tableName, fields, ','));
sbSql.AppendFormat("FROM ( SELECT TOP {0} ", pageSize * pageIndex);
sbSql.AppendFormat(" [_RowNum_] = ROW_NUMBER() OVER ( ORDER BY {0} ), ", collatingSequence);
sbSql.AppendFormat(" {0} ", key);
sbSql.AppendFormat(" FROM {0} ", tableName);
sbSql.AppendFormat(" {0} ", whereClause);
sbSql.AppendFormat(" ) AS [_TempTable_] ");
sbSql.AppendFormat(" INNER JOIN {0} ON [_TempTable_].{1} = {0}.{1} ", tableName, key);
sbSql.AppendFormat("WHERE [_TempTable_].[_RowNum_] > {0} ", pageSize * (pageIndex - 1));
sbSql.AppendFormat("ORDER BY [_TempTable_].[_RowNum_] ASC ");

return sbSql.ToString();
}

/// <summary>
/// 给字段添加表名前缀
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fields">字段</param>
/// <param name="separator">标识字段间的分隔符</param>
/// <returns></returns>
public static string PrependTableName(string tableName, string fields, char separator)
{
StringBuilder sbFields = new StringBuilder();

string[] fieldArr = fields.Trim(separator).Split(separator);
foreach (string str in fieldArr)
{
sbFields.AppendFormat("{0}.{1}{2}", tableName, str.Trim(), separator);
}

return sbFields.ToString().TrimEnd(separator);
}

假设有如下产品表:

CREATE TABLE [dbo].[Tbl_Product]
(
[ID] [int] IDENTITY(1, 1)
NOT NULL ,
[ProductId] [varchar](50) NOT NULL ,
[ProductName] [nvarchar](50) NOT NULL ,
[IsDeleted] [int] NOT NULL
CONSTRAINT [DF_Tbl_Product_IsDeleted] DEFAULT ( (0) ) ,
CONSTRAINT [PK_Tbl_Product] PRIMARY KEY CLUSTERED ( [ProductId] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
)
ON [PRIMARY]

  1. Tbl_Product->ID(序号,非空,自增)
  2. Tbl_Product->ProductId(产品Id,主键)
  3. Tbl_Product->ProductName(产品名称,非空)
  4. Tbl_Product->IsDeleted(虚拟删除标记,非空)

调用BasicFunction.GetPagingSQL("Tbl_Product", "ID", "ID,ProductId,ProductName", "IsDeleted=0", "ProductName ASC, ID DESC", 5, 5),BasicFunction为分页排序方法所在的静态类,生成的分页排序SQL语句如下(已手动调整了格式):

SELECT Tbl_Product.ID ,
Tbl_Product.ProductId ,
Tbl_Product.ProductName
FROM ( SELECT TOP 25
[_RowNum_] = ROW_NUMBER() OVER ( ORDER BY ProductName ASC, ID DESC ) ,
ID
FROM Tbl_Product
WHERE IsDeleted = 0
) AS [_TempTable_]
INNER JOIN Tbl_Product ON [_TempTable_].ID = Tbl_Product.ID
WHERE [_TempTable_].[_RowNum_] > 20
ORDER BY [_TempTable_].[_RowNum_] ASC

  • 查询的字段列表,去掉了不关心的字段(这里为IsDeleted,因为条件里面IsDeleted=0,查出来的产品都是没被删除的);
  • 排序依据,在调用该方法时,应尽量确保排序的依据可以唯一确定记录在结果集中的位置(这里添加了辅助排序依据,ID DESC,如果产品重名,添加的晚的排在前面);
  • 性能优化的一点儿建议:如果字段的值是统计出来的,通常是在视图中,且结果集很大,且需要对结果集分页排序,可以使用临时表规避不稳定的查询效率(关于不稳定的查询效率,会在之后的文章中示例);
  • 另外一点儿建议,使用ROW_NUMBER时,切记一定要和“TOP n”一起使用,n等于int.MaxValue都比不加“TOP n”时要快。

摘自:http://www.cnblogs.com/return8023/archive/2012/05/20/2510983.html