大家好,又见面了,我是全栈君。
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);
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/116265.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...