row_number() OVER (PARTITION BY COL1 ORDER BY COL2)「建议收藏」

全栈程序员-站长 • 2022年1月23日下午8:00 • 未分类 • 阅读 52

大家好，又见面了，我是全栈君。

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

发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/116265.html原文链接：https://javaforall.net