sql分组查询语句--行内分组(非聚合分组)

sql分组查询语句--行内分组（非聚合分组）

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

SQL> SELECT G.*, ROW_NUMBER() OVER(PARTITION BY a ,b ORDER BY c DESC) ROWN

2 from (select '1' a, '2' b, '1' c

3 from dual

4 union all

5 select '1', '2', '2'

6 from dual

7 union all

8 select '1', '3', '3'

9 from dual

10 union all

11 select '1', '3', '4'

12 from dual

13 union all

14 select '1', '4', '5' from dual

15 union all

16 select '1','3','5'from dual) G

17 ;

A B C ROWN

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

1 2 2 1

1 2 1 2

1 3 5 1

1 3 4 2

1 3 3 3

1 4 5 1

6 rows selected

与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．

row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．

rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number 是没有重复值的．

lag（arg1,arg2,arg3):

arg1是从其他行返回的表达式

arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。

arg3是在arg2表示的数目超出了分组的范围时返回的值。

语句一:

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