row_number() over (partition by....order by...)用法
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
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行。(还有一个例子)
相关推荐
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY name || idNumber || TO_CHAR(date, 'YYYYMMDD') ORDER BY id) AS rn FROM teacher t ) subquery WHERE rn = 1; ``` - **PARTITION BY** 子句中的`name || idNumber...
row_number() over ([partition by col1] order by col2) as 别名 ``` 其中，`partition by col1` 用于指定分区的列，`order by col2` 用于指定排序的列。 在使用 row_number()over 函数时，需要注意以下几点： * ...
ROW_NUMBER() OVER (PARTITION BY COLUMN ORDER BY COLUMN) 其中，PARTITION BY COLUMN 是用于分组的列名，ORDER BY COLUMN 是用于排序的列名。ROW_NUMBER() 函数从 1 开始，为每一条分组记录返回一个数字。 示例...
ROW_NUMBER() OVER (PARTITION BY <column_list> ORDER BY <order_by_expression>) ``` - **PARTITION BY**：用于指定按照哪些列进行分组。 - **ORDER BY**：用于指定如何对分组内的行进行排序。 #### 三、Row_...
第二步，我们添加row_number() over(partition by outer_code order by outer_code)来标识重复记录，这将为同一`outer_code`下的每一行分配一个唯一的行号。第三步，我们可以利用这个行号来删除重复数据，只保留每组...
SELECT *, ROW_NUMBER() OVER(PARTITION BY corp_name ORDER BY submit_time DESC) AS row_num FROM your_table ``` 这段SQL将返回一个新的列 `row_num`，它在每个 `corp_name` 分区内按 `submit_time` 的降序排列...
SELECT ROW_NUMBER() OVER (ORDER BY column_name(s)) AS row_num, other_columns FROM table_name; ``` 其中，`OVER`子句定义了排序的逻辑窗口，`ORDER BY`子句则指定排序的依据。`column_name(s)`是你想要按照...
`ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS Seq` - `PARTITION BY` 子句用于将数据集分割成多个分区，每个分区内的行具有相同的值。 - `ORDER BY` 子句则定义了在每个分区内部如何对行进行排序。 2...
SELECT *, ROW_NUMBER() OVER (PARTITION BY deptid ORDER BY salary DESC) AS rank FROM employee; ``` 这条 SQL 语句的作用是： - 使用 `PARTITION BY deptid` 对数据按照 `deptid` 进行分组。 - 使用 `ORDER BY...
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS num FROM #score ) T WHERE T.num ORDER BY subject ``` 在这个查询中，`PARTITION BY subject` 将数据按科目分组，`ORDER BY score DESC` 在...
介绍了 row_number() over(order by column asc) 函数和 row_number() over(partition by column1 order by column2 asc) 的使用实例和方法
row_number() over (order by rownum) as row_number from emp; ``` 在这个例子中，`row_number()`函数返回的序号与`rownum`列的值相同，但它们的计算方式不同。`rownum`是在查询结果产生后添加的，而`row_number...
ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>) ``` - **PARTITION BY 子句**：用于指定数据集中的分区。如果省略了此子句，则整个结果集被视为一个分区。 - **ORDER BY 子句**：用于确定ROW_...
select *，row_number() over(partition by column1 order by column2) as n from tablename 在上面语法中： PARTITION BY子句将结果集划分为分区。 ROW_NUMBER()函数分别应用于每个分区，并重新初始化每个分区的...
#### 三、OVER (PARTITION BY ..) 的使用方法 ##### 1. 分区概述 `OVER (PARTITION BY ...)` 的主要作用是定义一个分区集合，即对数据进行分组，然后在每个分区内执行相应的操作。例如，在以下示例中，我们根据 `...
ROW_NUMBER() OVER (ORDER BY a.CreateTime DESC) AS RowNumber FROM table_name AS a ) SELECT * FROM CTE WHERE RowNumber BETWEEN @startIndex AND @endIndex ORDER BY RowNumber; ``` 在这个例子中，`CTE`...
ROW_NUMBER() OVER([ <partition_by_clause>] <order_by_clause>) ``` 这里，`<partition_by_clause>` 可以用来将结果集分割成多个分区，每个分区内部的行将独立进行编号。而 `<order_by_clause>` 用于指定在每个...
ROW_NUMBER() OVER ( [PARTITION BY partition_expression, ...] ORDER BY order_by_expression [ASC | DESC], ... ) ``` - **PARTITION BY**：可选参数，用于指定分组字段。当指定了这个参数时，`row_number()` 将...
row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。 与rownum的区别在于：使用rownum进行排序的...