oracle中分组后取各组的前n条记录的sql语句:rownumber()over()(二)
3000 4
20 3000 4
30 950 1
30 1250 2
30 1250 2
30 1500 3
30 1600 4
30 2850 5
已选择14行。
语句六：
select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) as lag_ from scott.emp order by deptno;
www.2cto.com
执行结果：
DEPTNO ENAME SAL LAG_
---------- -------------------- ---------- --------------------
10 CLARK 2450
10 KING 5000 CLARK
10 MILLER 1300 KING
20 ADAMS 1100
20 FORD 3000 ADAMS
20 JONES 2975 FORD
20 SCOTT 3000 JONES
20 SMITH 800 SCOTT
30 ALLEN 1600
30 BLAKE 2850 ALLEN
30 JAMES 950 BLAKE
30 MARTIN 1250 JAMES
30 TURNER 1500 MARTIN
30 WARD 1250 TURNER
www.2cto.com
已选择14行。