三种分页算法

-----读取31到40条的记录（SQLServer,以自动增长的CompanyID作为主键,注意：ID可能不是连续的)

 

----在MSSQL2005下可用

WITH List AS (
 SELECT ROW_NUMBER() OVER (ORDER BY c.CompanyID DESC) AS Num,c.CompanyName,c.CompanyID
   FROM Company c
 )
 SELECT Num,CompanyName,CompanyID FROM List WHERE Num>=31 AND Num<=40;

 

 

-----MSSQL2005下版本的数据库可用

 

---not in 方法： 缺点排序要以自动编号，数字来排 数据量大时性能最差

SELECT TOP 10 CompanyName,CompanyID FROM Company c WHERE c.CompanyID NOT IN (
  SELECT TOP 30  c1.CompanyID FROM  Company c1 ORDER BY c1.CompanyID DESC
 ) ORDER BY c.CompanyID DESC;
 

 

-----ID比较法 缺点排序要以自动编号，数字来排 
SELECT TOP 10 c1.CompanyName,c1.CompanyID FROM Company AS c1
 WHERE c1.CompanyID<
(
 SELECT MIN(CompanyID) FROM
      (SELECT TOP 30 c2.CompanyID FROM Company AS c2 ORDER BY c2.CompanyID DESC) AS c
  )
ORDER BY c1.CompanyID DESC;