本文是关于几类数据库限制结果集行数的使用方法,是在一本叫《程序员的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
检索第三行到第五行的数据:(同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