sql 查询排名 排名可并列

 需求：按照fs排名，可并列

dense_rank

dense_rank() over (order by  fs desc) ranks

 补充：

rank()  一般用不到,太特殊，两个并列第三名后面是第五名 而不是 第四名

---

拼接排名为字符串，使用CONCAT('第',base.ranks,'名')即可

with base as (
SELECT dense_rank() over (order by ks_ksh.fs desc) ranks,ks_ksh.fs,ksh.`name` kshname,ks.`name` ksname
FROM ks,ksh,ks_ksh,(SELECT MAX(fs) mm FROM ks_ksh kk WHERE kk.ks_id = 5 and kk.delete_flag = 0 GROUP BY kk.ksh_id) k2
where ks.id = 5 and ks.id = ks_ksh.ks_id and ksh.id = ks_ksh.ksh_id and k2.mm=ks_ksh.fs and ks_ksh.delete_flag =0
GROUP BY ks_ksh.ksh_id
ORDER BY ks_ksh.fs DESC
)
SELECT base.*,CONCAT('第',cast( base.ranks as CHAR),'名') as ranksName,case  (select count(1)  from  base  tb where tb.ranks = base.ranks )
when 1 then ''
else "并列"
end  type
from base