row_number() OVER (PARTITION BY COL1 ORDER BY COL2)

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

create table student (id int ,classes int ,score int);insert into student values(1,1,89);insert into student values(2,1,90);insert into student values(3,1,76);insert into student values(4,2,69);insert into student values(5,2,79);insert into student values(6,2,95);insert into student values(7,3,80);insert into student values(8,3,85);insert into student values(9,3,79);commit;

 

select t.* from student t;

--資料顯示為
id       classes      score
-------------------------------------------------------------
1           1          89
2           1          90
3           1          76
4           2          69
5           2          79
6           2          95
7           3          80
8           3          85
9           3          79

--需求：根據班級分組，顯示每個班的英語成績排名

--預期結果：

id       classes      score                              rank
----------- ----------- ---------------------------------------
3           1          76                                 1
1           1          89                                 2
2           1          90                                 3
4           2          69                                 1
5           2          79                                 2
6           2          95                                 3
9           3          79                                 1
7           3          80                                 2
8           3          85                                 3

--SQL指令碼：

SELECT *, Row_Number() OVER (partition by classes ORDER BY score desc) rank FROM student;

--查詢t_test表中，callid欄位沒有重複過的資料，效率高過group by having count

select t.*, t.rowid  from t_test t where t.rowid not in (select rid                         from (select t2.rowid rid,                                      row_number() over(partition by t2.callid order by t2.rowid desc) m                                 from t_test t2)                        where m <> 1)   and t.rowid not in (select rid                         from (select t2.rowid rid,                                      row_number() over(partition by t2.callid order by t2.rowid asc) m                                 from t_test t2)                        where m <> 1);