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