分页查询

--分页查询,要求表必须包含标识列
SELECT [要查询字段]
FROM [查询表名称],
(
    SELECT *
    FROM 
    ( 
        SELECT ROW_NUMBER() OVER(ORDER BY [排序字段]) as F_ID,[表标识列名称] AS F_ID_0 
        FROM [查询表名称] 
        WHERE [查询条件]
    ) AS [全部查询结果] 
    WHERE [全部查询结果].F_ID>=[分页记录开始号码] AND [全部查询结果].F_ID<[分页记录结束号码]
) as [分页结果] 
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();
        }