无论sqlserver 还是 oracle
用rank() 或者 dense_rank() 函数。
select name,score,rank() over(order by score) tt from t;
如果有并列的话 . 他们后边的排序会变成这样的。 根据你的需求选用。
1
2
2
4
select name,score, dense_rank() over(order by score) tt from t;
1
2
2
3
这三个函数区别开来
--rank() over(partition by a.regstudentid order by a.dtdate) new_flag,
--dense_rank() over(partition by a.regstudentid order by a.dtdate) new_flag,
--row_number() over(partition by a.regstudentid order by a.dtdate) new_flag,--当为1时是否首次报名(第几次出现)
补充:
row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)