数据库限制结果集行数（可用于分页）

本文是关于几类数据库限制结果集行数的使用方法，是在一本叫《程序员的SQL金典》书中看到的，认为有用，就按照自己的意思总结了一下。

 

MYSQL：

         select * from table_name order by col_namedesc/asc limit startline,counts

         select * from T_Employeeorder by FSalary desclimit startline,counts

例子：按照工资降序排列的前5条记录：

         select * from T_Employeeorder by FSalary desclimit 0,5

 

SQL Server 2000:

         select top counts * from table_name orderby col_name desc/asc

         select top 5 * fromT_Employee order by FSalary desc

检索从第六名开始一共3个人的信息：

         select top counts * from table_name wherecol_name not in(select top counts col_name from table_name order by col_namedesc/asc) order by col_name desc/asc

         select top 3 * fromT_Employee where FNumber not in (select top 5 FNumber from

T_Employee orderby FSalary desc) order by FSalary desc

 

SQL Server 2005:

         窗口函数row_number over(排序规则)

        select row_numberover(order by FSalary desc),FNumber,FName,FSalary,FAge from

T_Employee

检索第三行到第五行的数据：

         select * from (select row_number over(orderby col_name desc) as

col_alias,colname,…,fromtable_name ) as table_alias where table_alias.col_alias>=startline and table_alias.col_alias<=endline

         select * from (select row_number over(orderby FSalary desc) as rownum,

FNumber,FName,FSalary,FAgefrom T_Employee) as a

 where a.rownum>=3AND a.rownum<=5

 

Oracle:

         检索第三行到第五行的数据：(同SQL Server2005，但rownum应改为row_num,rownum是oracle的保留字)

         select * from (select row_number over(orderby FSalary desc) row_num,

FNumber,FName,FSalary,FAgefrom T_Employee) a

 wherea.row_num>=3 AND a.row_num<=5

保留字rownum：结果集中每一行的行号（从1开始），oracle取一定范围内的数据必须借助窗口函数row_number()。

 

DB2：

         (同SQL Server2005和Oracle)

select * from(select row_number over(orderby FSalary desc) row_num,

FNumber,FName,FSalary,FAgefrom T_Employee) a

 wherea.row_num>=3 AND a.row_num<=5

         但DB2还提供了fetch关键字用来提取结果集的前n行：(fetchfirst 条数 rows only)

                   select * fromT_Employee order by FSalary desc fetch first 6 rows only

         DB2检索从第六名共3个人的信息：

         select * from table_name where col_name notin(select col_name from table_name order by col_name desc fetch first countsrows only) order by col_name desc fetch first counts rows only

select * fromT_Employee where FNumber not in(select FNumber from T_Employee order by FSalary desc fetch first5 rows only) order byFSalary desc fetch first 3 rows only