在MySQL中排名联合职位 [英] Ranking joint positions in MySQL

问题描述

下面是我所拥有的

  userid score 
    1       8    
    2       5    
    3       4    
    4       4    
    5      10    
    6       3  

我想要的是如下

userid score position
    5      10     1
    1       8     2
    2       5     3
    3       4     4
    4       4     4
    6       3     5

注意:

我在输出下方创建的代码

I have code where I have created below output,

userid score position
    5      10     1
    1       8     2
    2       5     3
    3       4     4
    4       4     4
    6       3     6

代码是

SELECT userid, score, 
(SELECT COUNT(*) FROM fschema.mytab3 u2 
WHERE 
u2.score > u1.score) + 1 AS position FROM fschema.mytab3 u1
ORDER BY position

我希望用户6将position用作5，而不是6

I want user 6 to have position as 5 instead of 6

推荐答案

这怎么办?

SELECT userid, score, 
(SELECT COUNT(distinct u2.score) FROM fschema.mytab3 u2 
WHERE 
u2.score > u1.score) + 1 AS position FROM fschema.mytab3 u1
ORDER BY position

这篇关于在MySQL中排名联合职位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！