代码之家 › 专栏 › 技术社区 › Joey C.

查询用户在一对多表中的排名

mysql-error-1111 sql mysql

Joey C. · 技术社区 · 14 年前

我正在尝试编写一个查询来查找用户游戏的得分排名。我需要它接收一个用户id,然后将该用户的相对排名返回给其他用户的得分。有一个用户和一个游戏桌。游戏表有一个具有一对多关系的userId字段。

用户:
身份证freebee
1 10

游戏:
用户ID得分
1 15
1 20
2 10
1 15

目前我有:

SELECT outerU.id, (

SELECT COUNT( * )  
FROM users userI, games gameI  
WHERE userI.id = gameI.userId  
AND userO.id = gameO.userId  
AND (  
   userI.freebee + SUM(gameI.score)  
   ) >= ( userO.freebee + SUM(gameO.score) )  
) AS rank  
FROM users userO,  
games gameO  
WHERE id = $id

5 回复 | 直到 13 年前

a1ex07 14 年前

SELECT u.id,total_score,
 ( SELECT COUNT(*) FROM
    (SELECT u1.id, (IFNULL(u1.freebee,0)+ IFNULL(SUM(score),0)) as total_score
     FROM users u1
     LEFT JOIN games g ON (g.userId = u1.id)
     GROUP BY u1.id
    )x1
   WHERE x1.total_score > x.total_score
 )+1 as rank,

( SELECT COUNT(DISTINCT total_score) FROM
    (SELECT u1.id, (IFNULL(u1.freebee,0)+ IFNULL(SUM(score),0)) as total_score
     FROM users u1
     LEFT JOIN games g ON (g.userId_Id = u1.id)
     GROUP BY u1.id
    )x1
   WHERE x1.total_score > x.total_score
 )+1 as dns_rank

 FROM users u

 LEFT JOIN
  ( SELECT u1.id, (IFNULL(u1.freebee,0)+ IFNULL(SUM(score),0)) as total_score
    FROM users u1
    LEFT JOIN games g ON (g.userId = u1.id)
    GROUP BY u1.id
  )x ON (x.id = u.id)

rank -(正常等级-例如-1,2,2,4,5), dns_rank total_score

Thomas 14 年前

查询不喜欢Sum函数中外部表的引用 SUM(gameO.score)

编辑

根据你的新信息,我已经调整了我的查询。

Select U.id, U.freebee, GameRanks.Score, GameRanks.Rank
From users As U
    Join    (
            Select G.userid, G.score
                , (
                    Select Count(*)
                    From Games As G2
                    Where G2.userid = G.userid
                        And G2.Score > G.Score
                    ) + 1 As Rank
            From Games As G
            ) As GameRanks
        On GameRanks.userid = U.id
Where U.id =1

Tom H 14 年前

我不是一个MySQL的人,但是我相信通常的排名方法是在SQL语句中使用一个变量。如下所示(未经测试):

SELECT
    SQ.user_id,
    @rank:=@rank + 1 AS rank
FROM
(
    SELECT
        U.user_id,
        U.freebee + SUM(COALESCE(G.score, 0)) AS total_score
    FROM
        Users U
    LEFT OUTER JOIN Games G ON
        G.user_id = U.user_id
) SQ
ORDER BY
    SQ.total_score DESC

serg 14 年前

这是一个“简化”版本,只根据“游戏”表计算排名。为了计算特定游戏的排名,你只需要添加额外的连接。

SELECT COUNT(*) + 1 AS rank
FROM   (SELECT userid,
               SUM(score) AS total
        FROM   games
        GROUP  BY userid
        ORDER  BY total DESC) AS gamescore
WHERE  gamescore.total > (SELECT SUM(score)
                          FROM   games
                          WHERE  userid = 1)

这是基于 ranking == number of players with bigger score + 1

Jeffer 14 年前

http://rpbouman.blogspot.com/2009/09/mysql-another-ranking-trick.html