SQL 分頁－冠霖的部落格

微軟官方分頁寫法http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenethowto05.asp

//我在程式中的用法 ,Sno 是自動增加的int pagesize 是取top幾筆

#region SQLs
casestr = "where CompanyID=@CompanyID and EmpID=@EmpID and CloseFlag='Y' "; //where條件

                    SQLs.Append("select top " + pagesize.ToString() + " * , ");
                    SQLs.Append("(select count(*) from WorkList_Main " + casestr + " ) as TotalCnt "); //算count
                    SQLs.Append("from WorkList_Main ");
                    SQLs.Append(casestr);

                    if (GV3.PageIndex > 0)
                    {
                        SQLs.Append("and Sno > ");
                        SQLs.Append("(select max(Sno) from (select top " + (pagesize * GV3.PageIndex) + "Sno FROM WorkList_Main ");
                        SQLs.Append(casestr);
                        SQLs.Append("order by Sno) as WorkList_Main ) ");
                    }
                    SQLs.Append("order by Sno");
#endregion