oracle分组按最大值排序,SQL(oracle) 取得分组后最大值记录

语句:

语句一:

select row_number() over(order by sale/cnt desc) as sort, sale/cnt

from (

select -60 as sale,3 as cnt from dual union

select 24 as sale,6 as cnt from dual union

select 50 as sale,5 as cnt from dual union

select -20 as sale,2 as cnt from dual union

select 40 as sale,8 as cnt from dual);

执行结果:

SORT       SALE/CNT

---------- ----------

1             10

2              5

3              4

4            -10

5            -20

语句二:查询员工的工资,按部门排序

select ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from scott.emp;

执行结果:

ENAME                           SAL      SAL_ORDER

-------------------- ---------- ----------

KING                           5000              1

CLARK                          2450              2

MILLER                         1300              3

SCOTT                          3000              1

FORD                           3000              2

JONES                          2975              3

ADAMS                          1100              4

SMITH                           800              5

BLAKE                          2850              1

ALLEN                          1600              2

TURNER                         1500              3

WARD                           1250              4

MARTIN                         1250              5

JAMES                           950              6

已选择14行。

语句三:查询每个部门的最高工资

select deptno,ename,sal from

(select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order

from scott.emp) where sal_order <2;

执行结果:

DEPTNO ENAME                          SAL

---------- -------------------- ----------

10 KING                          5000

20 SCOTT                         3000

30 BLAKE                         2850

已选择3行。

语句四：

select deptno,sal,rank() over (partition by deptno order by sal) as rank_order from scott.emp order by deptno;

执行结果:

DEPTNO         SAL RANK_ORDER

---------- ---------- ----------

10        1300           1

10        2450           2

10        5000           3

20         800           1

20        1100           2

20        2975           3

20        3000           4

20        3000           4

30         950           1

30        1250           2

30        1250           2

30        1500           4

30        1600           5

30        2850           6

已选择14行。

语句五：

select deptno,sal,dense_rank() over(partition by deptno order by sal) as dense_rank_order from scott.emp order by deptn;

执行结果：

DEPTNO         SAL DENSE_RANK_ORDER

---------- ---------- ----------------

10        1300                 1

10        2450                 2

10        5000                 3

20         800                 1

20        1100                 2

20        2975                 3

20        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;

执行结果：

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

已选择14行。

SQL(oracle) 取得分组后最大值记录

标签：union   tin   最大值   没有   article   部门   net   ora   log

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：http://www.cnblogs.com/myjoan/p/6123304.html