.net SQL分页
1.分页SQL
declare @pagesize integer,@cpage integer; --变量定义 set @pagesize =200; --页码大小 set @cpage = 5; --当前页 ---@cpage 为 第一页的时候 --select top (@pagesize) * from Member order by ID desc ---- select top (@pagesize) * from Member where ID not in( select top ((@cpage - 1) * @pagesize) ID from Member order by ID desc ) order by ID desc
declare @pagesize integer,@cpage integer; set @pagesize =10; set @cpage = 1; SELECT TOP (@pagesize) * FROM ( SELECT row_number() over(order by AddTime) as row_number,* FROM ( SELECT TOP(100) a.AddTime, type = 0, SUM(b.account) AS balance FROM Orders a, CashierDetail b WHERE a.OrderId = b.OrderId AND b.MemberId= '39085' AND a.MemberId = b.MemberId AND b.Account > 0 GROUP BY a.addtime ORDER BY AddTime DESC UNION ALL SELECT TOP(100) AddTime as AddTime,1 as type,Balance as Balance FROM MemberRech WHERE MemberId = '39085' ORDER BY AddTime DESC ) Temp ) TEMPTABLE WHERE 1=1 AND row_number NOT IN ( SELECT TOP ((@cpage - 1) * @pagesize) row_number FROM ( SELECT row_number() over(order by AddTime) as row_number,* FROM ( SELECT TOP(100) a.AddTime, type = 0, SUM(b.account) AS balance FROM Orders a, CashierDetail b WHERE a.OrderId = b.OrderId AND b.MemberId= '39085' AND a.MemberId = b.MemberId AND b.Account > 0 GROUP BY a.addtime ORDER BY AddTime DESC UNION ALL SELECT TOP(100) AddTime as AddTime,1 as type,Balance as Balance FROM MemberRech WHERE MemberId = '39085' ORDER BY AddTime DESC ) Temp ) TEMPTABLE WHERE 1=1 ORDER BY row_number DESC ) ORDER BY row_number DESC