从 2 个表中计算高分排名

发布于 2024-09-17 21:14:44 字数 637 浏览 0 评论 0原文

我有一个问答游戏，我想奖励用户 2 项活动：
1）正确回答
2）向问题池发送问题

我想查询特定玩家的分数和排名，我使用这个查询：

SELECT (correct*10+sent*30) AS score, @rank:=@rank+1 AS rank 
FROM   (  trivia_players 
          JOIN (  SELECT COUNT(*) AS sent, senderid 
                  FROM trivia_questions 
                  WHERE senderid='$userid'
               ) a 
          ON trivia_players.userid=a.senderid
       )  
       ORDER BY score DESC

如果玩家在两个表中，即正确回答并发送了问题，则它有效。但如果玩家没有发送问题，它就不起作用

，不知道如何解决此查询？（$userid 是给定的参数）

谢谢！

谢谢汤姆！唯一的问题是排名不正确：
用户ID得分排名
58217 380 1
12354 80 3
32324 0 2

原文

i have a trivia game and i want to reward users for 2 events:
1) answering correctly
2) sending a question to the questions pool

i want to query for score and rank of a specific player and i use this query:

SELECT (correct*10+sent*30) AS score, @rank:=@rank+1 AS rank 
FROM   (  trivia_players 
          JOIN (  SELECT COUNT(*) AS sent, senderid 
                  FROM trivia_questions 
                  WHERE senderid='$userid'
               ) a 
          ON trivia_players.userid=a.senderid
       )  
       ORDER BY score DESC

and it works if the player is in both tables i.e answered correctly AND sent a question.
but it doesn't work if a player hasn't sent a question

any idea how to fix this query? ($userid is the given parameter)

thanks!

Thanks Tom! only problem is the ranks are not correct:
userid score rank
58217 380 1
12354 80 3
32324 0 2

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

絕版丫頭 2024-09-24 21:14:44

我可能会这样做：

SELECT
    user_id,
    score,
    rank
FROM
(
    SELECT
        TP.user_id,
        (TP.correct * 10) + (COUNT(TQ.sender_id) * 30) AS score,
        @rank:=@rank + 1 AS rank
    FROM
        Trivia_Players TP
    LEFT OUTER JOIN Trivia_Questions TQ ON
        TQ.sender_id = TP.user_id
    GROUP BY
        TP.user_id,
        TP.correct
    ORDER BY
        score DESC
) AS SQ
WHERE
    SQ.user_id = $user_id

我不太使用MySQL，所以语法可能不完美。我认为你可以在 MySQL 中使用这样的子查询。假设 MySQL 通过仅计算具有非空值的行来处理 COUNT()，这应该可以工作。

关键是您对 Trivia Questions 中的非空列执行 COUNT 操作，以便由用户对它们进行计数，并且您需要使用子查询，以便您可以在限制特定用户 ID 之前获得每个人的排名。

I would probably do it like this:

SELECT
    user_id,
    score,
    rank
FROM
(
    SELECT
        TP.user_id,
        (TP.correct * 10) + (COUNT(TQ.sender_id) * 30) AS score,
        @rank:=@rank + 1 AS rank
    FROM
        Trivia_Players TP
    LEFT OUTER JOIN Trivia_Questions TQ ON
        TQ.sender_id = TP.user_id
    GROUP BY
        TP.user_id,
        TP.correct
    ORDER BY
        score DESC
) AS SQ
WHERE
    SQ.user_id = $user_id

I don't use MySQL much, so the syntax may not be perfect. I think that you can use a subquery like this in MySQL. Assuming that MySQL handles COUNT() by only counting rows with a non-null value for , this should work.

The keys are that you do a COUNT over a non-null column from Trivia Questions so that it counts them up by the user and you need to use a subquery so that you can get ranks for everyone BEFORE constraining to a particular user id.

回复收藏 0 原文