分页查询

 1 --分页查询,要求表必须包含标识列
 2 SELECT [要查询字段]
 3 FROM [查询表名称],
 4 (
 5     SELECT *
 6     FROM 
 7     ( 
 8         SELECT ROW_NUMBER() OVER(ORDER BY [排序字段]) as F_ID,[表标识列名称] AS F_ID_0 
 9         FROM [查询表名称] 
10         WHERE [查询条件]
11     ) AS [全部查询结果] 
12     WHERE [全部查询结果].F_ID>=[分页记录开始号码] AND [全部查询结果].F_ID<[分页记录结束号码]
13 ) as [分页结果] 
14 where [分页结果].F_ID_0=[表标识列名称]

 /// <summary>
/// 生成分页SQL查询语句,SQL Server 2005
/// </summary>
/// <param name="TablesName">表名称,单表或者多表</param>
/// <param name="QueryFileds">查询的字段,多表查询时最好指定全名,形如:[表名称1.字段名称,表名称2.字段名称,...]</param>
/// <param name="Where">查询条件语</param>
/// <param name="IdFields">单表或多表的标识列名称</param>
/// <param name="OrderBy">排序语句,不带order by</param>
/// <param name="Start">结果的开始行号</param>
/// <param name="End">结果的开始行号</param>
/// <param name="TempTableName">指定一个临时表的名称,为了防止与现有表重名</param>
/// <param name="TempIdName">指定一个临时字段列名称,为了防止与现有字段重名</param>
/// <returns></returns>
        public static string BuilderTablesQueryString(List<string> TablesName,string QueryFileds, string Where, List<string> IdFields, string OrderBy, int Start, int End,string TempTableName,string TempIdName)
        {
            StringBuilder rtun = new StringBuilder();
//QueryFields="城市表.城市名称,省份表.省份名称,国家表.国家名称"--Type:string
//SELECT * FROM 城市表.城市名称,省份表.省份名称,国家表.国家名称,
            rtun.Append("SELECT ");
            rtun.Append(QueryFileds);


//TablesName="城市表:省份表:国家表"--Type:List<string>
//FROM "城市表,省份表,国家表",(
            rtun.Append(" FROM ");
for (int i = 0; i < TablesName.Count; i++)
            {
                rtun.Append(TablesName[i]);
                rtun.Append(",");
            }
            rtun.Append("(");
//TempIdName="ROW_ID"
//OrderBy="国家表.ID DESC,省份表.ID DESC,城市表.ID DESC"
//SELECT * FROM (SELECT ROW_NUMBER() OVER(ODERBY 国家表.ID DESC,省份表.ID DESC,城市表.ID DESC) AS ROW_ID
            rtun.Append(" SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ");
            rtun.Append(OrderBy);
            rtun.Append(") as ");
            rtun.Append(TempIdName);

//IdFields="国家表.ID:省份表.ID:城市表.ID"--Type:List<string>
//,国家表.ID AS ROW_ID_0,省份表.ID AS ROW_ID_1,城市表.ID AS ROW_ID_2
            for (int i = 0; i < IdFields.Count; i++)
            {
                rtun.Append(",");
                rtun.Append(IdFields[i]);
                rtun.Append(" AS ");
                rtun.Append(TempIdName);
                rtun.Append("_");
                rtun.Append(i.ToString());
            }

//FROM 国家表,省份表,城市表
            rtun.Append(" FROM ");
for (int i = 0; i < TablesName.Count; i++)
            {
                rtun.Append(TablesName[i]);
if (i < TablesName.Count - 1)
                {
                    rtun.Append(",");
                }
            }

//Where="国家表.ID=省份表.CID AND 城市表.PID=省份表.ID"
            if (Where != null)
            {
                rtun.Append(" WHERE ");
                rtun.Append(Where);
            }

//TempTableName="T_RSUT"
// ) AS T_RSUT1 WHERE T_RSUT1.ROW_ID>=100 AND T_RSUT1.ROW_ID<1025 ) AS T_RSUT2 WHERE
            rtun.Append(") AS " + TempTableName+"1");
            rtun.Append(" WHERE ");
            rtun.Append(TempTableName);
            rtun.Append("1.");
            rtun.Append(TempIdName);
            rtun.Append(">=");
            rtun.Append(Start.ToString());
            rtun.Append(" AND ");
            rtun.Append(TempTableName);
            rtun.Append("1.");
            rtun.Append(TempIdName);
            rtun.Append("<");
            rtun.Append(End.ToString());
            rtun.Append(") as ");
            rtun.Append(TempTableName);
            rtun.Append("2 where");

//WHERE T_RSUT2.ROW_ID_0=国家表.ID AND 城市表.ID=T_RSUT2.ROW_ID_1 AND 省份表.ID=T_RUST.ROW_ID2
            for (int i = 0; i < IdFields.Count; i++)
            {
                rtun.Append(" ");
                rtun.Append(TempTableName);
                rtun.Append("2.");
                rtun.Append(TempIdName);
                rtun.Append("_");
                rtun.Append(i.ToString());
                rtun.Append("=");
                rtun.Append(IdFields[i]);
if (i < IdFields.Count - 1)
                {
                    rtun.Append(" AND ");
                }
            }
            rtun.Append(";");
return rtun.ToString();
        }

转载于:https://www.cnblogs.com/CodeBase/archive/2011/10/25/2223865.html