gpt4 book ai didi

MySQL 嵌套查询计数

转载 作者:行者123 更新时间:2023-11-29 06:08:12 24 4
gpt4 key购买 nike

一些背景信息;这是一个允许用户创建挑战然后对这些挑战进行投票的应用程序(bog 标准 userX-vs-userY 类型应用程序)。

这里的最终目标是获取 5 个用户的列表,按他们赢得的挑战数量排序,以创建一种排行榜。如果挑战的状态 = 已过期,并且用户对该挑战的投票数超过 50 票,则该用户将赢得该挑战(挑战在总计 100 票后过期)。

我将在这里稍微简化一下,但本质上有三个表:

  1. 用户

    • 编号
    • 用户名
    • ...
  2. 挑战

    • 编号
    • 颁发给
    • 发布者
    • 状态
  3. challenges_votes

    • 编号
    • challenge_id
    • 用户 ID
    • 投票赞成

到目前为止,我有一个内部查询,如下所示:

SELECT `challenges`.`id`
FROM `challenges_votes`
LEFT JOIN `challenges` ON (`challenges`.`id` = `challenges_votes`.`challenge_id`)
WHERE `voted_for` = 1
WHERE `challenges`.`status` = 'expired'
GROUP BY `challenges`.`id`
HAVING COUNT(`challenges_votes`.`id`) > 50

在此示例中,将返回已过期的挑战 ID,并且 ID 为 1 的用户拥有超过 50 票。

我需要做的是计算此处返回的行数,将其应用于用户表中的每个用户,按返回的行数排序并将其限制为 5。

为此我有以下查询:

SELECT `users`.`id`, `users`.`username`, COUNT(*) AS challenges_won
FROM (
SELECT `challenges`.`id`
FROM `challenges_votes`
LEFT JOIN `challenges` ON (`challenges`.`id` = `challenges_votes`.`challenge_id`)
WHERE `voted_for` = 1
GROUP BY `challenges`.`id`
HAVING COUNT(`challenges_votes`.`id`) > 0
) AS challenges_won, `users`
GROUP BY `users`.`id`
ORDER BY challenges_won
LIMIT 5

这有点接近了,但是当然,这里的 voted_for 用户 ID 始终是 1。这是否是处理此类查询的正确方法?谁能告诉我应该如何做?

谢谢!

最佳答案

我想下面的脚本可以解决您的问题:

-- get the number of chalenges won by each user and return top 5
SELECT usr.id, usr.username, COUNT(*) AS challenges_won
FROM users usr
JOIN (
SELECT vot.challenge_id, vot.voted_for
FROM challenges_votes vot
WHERE vot.challenge_id IN ( -- is this check really necessary?
SELECT cha.id -- if any user is voted 51 he wins, so
FROM challenges cha -- why wait another 49 votes that won't
WHERE cha.status = 'expired' -- change the result?
) --
GROUP BY vot.challenge_id
HAVING COUNT(*) > 50
) aux ON (aux.voted_for = usr.id)
GROUP BY usr.id, usr.username
ORDER BY achallenges_won DESC LIMIT 5;

请允许我对结束挑战的条件提出一个小小的考虑:如果任何用户在 51 票后获胜,为什么需要再等待 49 票而不会改变结果?如果可以删除此约束,您就不必检查challenges表,这可以提高查询性能——但是,它也可能会变得更糟,您只能在使用实际数据库进行测试后才能知道。

关于MySQL 嵌套查询计数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10435719/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com