SQL 2005分页

最新推荐文章于 2024-11-22 10:11:53 发布

原创最新推荐文章于 2024-11-22 10:11:53 发布 · 326 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #object #string #null

ASP.NET 专栏收录该内容

26 篇文章

订阅专栏

本文介绍了一种使用SQL进行分页查询的方法，并详细解释了如何计算总页数及当前页显示的数据范围。通过Row_number()函数为记录排序并结合子查询实现分页效果，同时提供了动态SQL字符串构造示例。

/*CredentialNo--表DXXueLi的字段*/

select * from (select Row_number() Over(Order by CredentialNo) as DataNo,* from DXXueLi where 1=1 ) as Rank where DataNo between 21 and 40 Order by CredentialNo

/*************************/

//计算总页数
            PageCount = -1;
            AllCount = -1;
            string CountSql = "select count(*) from DXXueLi where 1=1 " + sqlStr;
            object tempo_result = (int)DBHelp.ScalarRun(CommandType.Text, CountSql, _param, 200);
            if (tempo_result != null)
            {
                PageCount = (int)tempo_result;

AllCount = PageCount;

                if ((PageCount % PageSize) > 0)
                {
                    PageCount = (PageCount / PageSize) + 1;
                }
                else
                {
                    PageCount = PageCount / PageSize;
                }

                if (PageCount < CurrentPage)
                {
                    CurrentPage = CurrentPage - 1;
                }

                //形成SQL语句
                sqlStr = "select * from (select Row_number() Over(Order by " + StrOrder + ") as DataNo,* from DXXueLi where 1=1   " + sqlStr;
                sqlStr = sqlStr + ") as Rank where DataNo between " + (PageSize * (CurrentPage - 1) + 1).ToString() + " and " + (CurrentPage * PageSize).ToString() + " Order by" + StrOrder;
            }