原始表(mytest)
| id | uid | date | score |
|---|---|---|---|
| 1 | 2 | 2023-06-07 | 50 |
| 2 | 3 | 2023-06-07 | 10 |
| 3 | 2 | 2023-06-21 | 55 |
| 4 | 2 | 2023-06-21 | 50 |
| 5 | 3 | 2023-06-06 | 45 |
| 6 | 2 | 2023-06-23 | 26 |
select
zb.*,
cb.topn as od1,-- 排名不间断
cb1.topn as od2,-- 组内排名
cb2.topn as od3-- 间断相同排名
from
mytest zb
left join(
select score,(select COUNT(*)+ 1 from (select distinct score from mytest) b
where b.score>a.score ) as topn
from(
select distinct score from mytest
) a
) cb on zb.score = cb.score
left join (
select uid,score,(select COUNT(*)+ 1 from (select distinct uid,score from mytest) b
where a.uid = b.uid and b.score>a.score) as topn
from
(
select distinct uid,score from mytest
) a
) cb1 on cb1.uid = zb.uid and zb.score = cb1.score
left join (
select a.id,(select count(*)+ 1 from mytest b where a.score<b.score and a.id <> b.id) topn
from mytest a
) cb2 on cb2.id = zb.id-- 这里不能用uid和score来判断 因为原始表socre有重复数据
order by zb.score desc