/// <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 [_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]

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 [_RowNum_] > 20 ORDER BY [_TempTable_].[_RowNum_] ASC