Select * From ( Select a.* , row_number() over(Order By field) rk From tablename a )Where rk<=10 row_number() over (partition order by filed)row_number是分析函数. 就是说: tablename 按照field排序之后, row_number() 取值就是排序后的. 第一为1, 第二为2. 如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。 Select * from (select depno,ename,sal,row_number() over (partition by depno order by sal desc) rn from emp) where rn<=38i以上版本,分析函数lag与lead可以提取后一条或前一天记录到本记录。 Select deptno,ename,hiredate,lag(hiredate,1,null) over (partition by deptno order by hiredate,ename) last_hire from emp order by depno,hiredate
OVER不是函数,它是分析函数的一个关键字,分析函数的语法是: FUNCTION_NAME(<argument>,<argument>...) OVER (<Partition-Clause><Order-By-Clause><Windowing Clause>)select * from t; x y 1 1 3 1 1 2 1 1 3 1select rank() over(order by x)a,x,y from t; a x y 1 1 1 1 1 2 1 1 1 4 3 1 4 3 1select row_number() over(order by x)a,x,y from t; a x y 1 1 1 2 1 2 3 1 1 4 3 1 5 3 1
Select a.* , row_number() over(Order By field) rk From tablename a
)Where rk<=10 row_number() over (partition order by filed)row_number是分析函数.
就是说: tablename 按照field排序之后, row_number() 取值就是排序后的. 第一为1, 第二为2.
如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。
Select * from
(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
from emp)
where rn<=38i以上版本,分析函数lag与lead可以提取后一条或前一天记录到本记录。
Select deptno,ename,hiredate,lag(hiredate,1,null) over
(partition by deptno order by hiredate,ename) last_hire
from emp
order by depno,hiredate
FUNCTION_NAME(<argument>,<argument>...)
OVER
(<Partition-Clause><Order-By-Clause><Windowing Clause>)select * from t;
x y
1 1
3 1
1 2
1 1
3 1select rank() over(order by x)a,x,y from t;
a x y
1 1 1
1 1 2
1 1 1
4 3 1
4 3 1select row_number() over(order by x)a,x,y from t;
a x y
1 1 1
2 1 2
3 1 1
4 3 1
5 3 1
http://61.144.28.248/dev/bbs_content.jsp?bbs_sn=7101&bbs_page_no=42&bbs_id=0060