主页 / dba / 问题 / 90190
Accepted
Dannymh
Asked: 2015-01-27 06:45:11 +0800 CST

根据排名选择、排名和应用分数

  • 772

我正在编写一个基于用户投票的 MVP 系统。桌子看起来像

投票 | 游戏 | 年
---------- ------ ------
玩家 1 | 2 | 2015年
玩家 1 | 2 | 2015年
玩家 2 | 2 | 2015年
玩家 2 | 2 | 2015年
玩家 2 | 2 | 2015年
玩家 3 | 2 | 2015年
玩家 1 | 2 | 2015年
玩家 3 | 2 | 2015年
玩家 4 | 2 | 2015年

此处的条目等同于投票。因此，我们将分组并计算出

玩家 1 = 3
玩家 2 = 3
玩家 3 = 2
玩家 4 = 1

MVP系统是一个“3-2-1”的评分系统。因此，积分以相反的顺序授予玩家。所以它应该只将获胜分数应用于前 3 个位置。IE

第 1 名得 3 分
第二名得2分
第三名得1分

当出现平局时，这会变得非常复杂，但前 3 名应该获得 MVP 积分。

在上述情况下，点差将是

玩家 1 = 2.5 分 玩家 2 = 2.5 分 玩家 3 = 1 分

这是因为前 2 名玩家实际上是第 1 名和第 2 名，没有第二名，所以下一个人获得第 3 名得 1 分。

有效（点数 = points_available / players_in_rank）

然而，这个等式是错误的。如果我们获得并列第 3 名，情况会变得更加复杂，最终可能会有很多人排在第 3 名，因此他们得到 1 分。

当你在第一或第二有领带时，这真的很复杂。我在 PHP 代码中解决这个问题很糟糕，但希望能够在 SQL 中执行此操作并取消 PHP。

只是想知道是否有人对此有任何简单的方法？

然后将这些分数添加到总体排名表中

问候丹

mysql rank

1 个回答

  1. Best Answer

    样本数据：

    CREATE TABLE v
    (`vote` varchar(8), `game` int, `year` int)
;

INSERT INTO v
    (`vote`, `game`, `year`)
VALUES
    ('player 1', 2, 2015),
    ('player 1', 2, 2015),
    ('player 2', 2, 2015),
    ('player 2', 2, 2015),
    ('player 2', 2, 2015),
    ('player 3', 2, 2015),
    ('player 1', 2, 2015),
    ('player 3', 2, 2015),
    ('player 4', 2, 2015)
;

    询问：

    SELECT 
vote,
no_votes,
rank,
3 - rank + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points
FROM (

    SELECT 
    sq.*
    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes
    FROM
    (

        SELECT 
        vote, 
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    ) sq
    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
    ORDER BY no_votes DESC

) q

    结果：

    |     VOTE | NO_VOTES | RANK | POINTS |
|----------|----------|------|--------|
| player 1 |        3 |    1 |    2.5 |
| player 2 |        3 |    1 |    2.5 |
| player 3 |        2 |    2 |      2 |
| player 4 |        1 |    3 |      1 |

    解释：

    这个

            SELECT 
        vote, 
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    很简单，我希望:)

    这个

    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery

    只是一个初始化一些变量的子查询。

    这里

        , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes

    第一行中@prevNoVotes包含前一行的值（或来自初始化子查询的值）。@rc1,@rc2并且@rc3只是排名计数器。他们计算每个等级，有多少人在同一等级。
    在最后一行，当前行的值被分配给变量。当处理下一行时，变量将保存这部分代码第一行中上一行的值。

    最后，在查询的最外部，我们计算点分布。

    随意问任何问题 ：）

    更新：

    添加了此表：

    CREATE TABLE points
    (`r` int, `p` int)
;

INSERT INTO points
    (`r`, `p`)
VALUES
    (1, 3),
    (2, 2),
    (3, 1)
;

    添加了更多逻辑:)

    SELECT 
vote,
no_votes,
rank,
@rc := @rc + 1 AS rowcounter,
((SELECT p FROM points WHERE r = IF(rank = @prevRank, rank, @rc)) - 1) + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points,
@prevRank := rank
FROM (
    SELECT 
    sq.*
    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes
    FROM
    (

        SELECT 
        vote, 
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    ) sq
    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
    ORDER BY no_votes DESC
)q
, (SELECT @rc := 0, @prevRank := 1) row_counter_init
ORDER BY rank
    • 1

相关问题