參考原文：http://blog.csdn.net/wanglipo/article/details/6954915

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根據COL1分組，在分組內部根據 COL2排序，而此函數計算的值就表示每組內部排序后的順序編號（組內連續的唯一的). 　　

??????與rownum的區別在于：使用rownum進行排序的時候是先對結果集加入偽列rownum然后再進行排序，而此函數在包含排序從句后是先排序再計算行號碼．　　row_number()和rownum差不多，功能更強一點（可以在各個分組內從1開時排序）．　　

????? rank()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內）．　　

????? dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名。相比之下row_number是沒有重復值的 ．　　

????? lag（arg1,arg2,arg3): arg1是從其他行返回的表達式 arg2是希望檢索的當前行分區的偏移量。是一個正的偏移量，時一個往回檢索以前的行的數目。 arg3是在arg2表示的數目超出了分組的范圍時返回的值。

create table a ( id number(8) not null, val number(8) ) /

insert into a(id,val) values(1,5);

insert into a(id,val) values(2,8);

insert into a(id,val) values(3,8);

?insert into a(id,val) values(5,8);

?insert into a(id,val) values(9,6);

insert into a(id,val) values(11,6);

insert into a(id,val) values(22,8);

insert into a(id,val) values(40,8);

?insert into a(id,val) values(41,5);

commit;

select * from a order by id;

--[查詢語句]--

select val,count(*) from ( select id,val,row_number() over (order by id) - row_number() over (partition by val order by id) x from a ) group by val,x order by min(id);

--[解析]--

select id,val,row_number() over (order by id) x from a; //按id進行排序

select id,val,row_number() over (partition by val order by id) x from a; //按val分組，分組內按id排序

select id,val,row_number() over (order by id) - row_number() over (partition by val order by id) x from a;//id排序值 減去 val分組內id排序值 = 連續相同值的排序值

?

原理：因為dense_rank() 和rownum都是連續的計數的，一個是全局計數，一個是局部分組計數，因此，兩個遞增頻率都是1的連續相減，值應該是一樣的，

比如 全局為 1,2,3,4,5

分組為 1,2? ；1;1,2???? 結果? 1-1=0,2-2=0； 3-1=2；? 4-1=3,5-2=3； 因此 1,2?；4,5是連續的

?

?

--統計一個手機連續在一個地區停留時間 created_time 定位時間 area_no 地區

create table T_test
(?
? MOBILE_NO????????? VARCHAR2(16),
? AREA_NO??????????? VARCHAR2(10),?
? CREATED_TIME?????? DATE not null
)；

?

寫法一：
select mobile_no, area_no, trunc(max_date - min_date), min_date, max_date
? from (select distinct mobile_no,
??????????????????????? area_no,
??????????????????????? min(created_time) over(partition by mobile_no, area_no, gn) min_date,
??????????????????????? max(created_time) over(partition by mobile_no, area_no, gn) max_date
????????? from (select rownum - dense_rank() over(partition by mobile_no, area_no order by created_time) gn,
?????????????????????? mobile_no,
?????????????????????? area_no,
?????????????????????? created_time
????????????????? from (select a.mobile_no, a.area_no, a.created_time
????????????????????????? from t_test a
???????????????????????? order by mobile_no, created_time)));

寫法二：

select mobile_no,
?????? area_no,
?????? count(*),
?????? min(created_time),
?????? max(created_time),
?????? max(created_time) - min(created_time)
? from (select a.mobile_no,
?????????????? a.area_no,
?????????????? a.created_time,
?????????????? row_number() over(order by created_time) - row_number() over(partition by area_no order by created_time) x
????????? from t_test a)
?group by mobile_no, area_no, x
?order by min(created_time)

?

我自己遇到的使用場景：

需求：查詢5月15日之前每一天分數最高的所有信息，但是某一天分數最高的信息可能有多條。

以下為我的sql，可以按時間倒序排列，并且給每天分數相同的序號是一致的。

select dense_rank() over (partition by cake.gamble_date order by cake.score desc) num,cake.*
from t_cake cake
where cake.cake_date<'5月15日'?
order by cake.cake_date desc;

?

總結

以上是生活随笔為你收集整理的oracle排序使用，很多中函数，不同的效果的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。