douzhuang2570 2017-04-18 17:47
浏览 93

按等级为多个游戏的玩家分配点数

I'm building a multi-game tournament scoreboard, where many players can play many games, many times. I want to assign points to players per game based on their rank for each game, not their actual score.

e.g.

Donkey Kong

Rank | Player  | Score  | Points Awarded    
1    | Player2 | 34,000 | 1,000    
2    | Player1 | 32,000 | 999    
3    | Player3 | 29,000 | 998

Robotron

Rank | Player  | Score  | Points Awarded    
1    | Player1 | 39,000 | 1,000    
2    | Player3 | 32,000 | 999    
3    | Player2 | 21,000 | 998

Tournament Standings

Player1 - 1,999 Points

Player2 - 1,998 Points

Player3 - 1,997 Points

So far I have ranking and points calculations working just fine...

SELECT 
    `id`, 
    `userID`, 
    `gameID`, 
    `gamescore`, 
    `rank`, 
    1001.0 - (rank) AS points
FROM (
    SELECT 
        `id`, 
        `userID`, 
        `gameID`, 
        `gamescore`, 
        @curr_rank := IF(@prev_rank = id, @curr_rank, @curr_rank + 1) AS rank, 
        @prev_rank := id
    FROM
        `submit_score`, 
        (SELECT @curr_rank := 0) y, 
        (SELECT @prev_rank := NULL) z
    WHERE `submit_score`.`tournID` = 2
    ORDER BY `gamescore` DESC 
) ranked_game;

But I need to be able to assign the points by rank-per-game and then have a grand total of points for each player that I can then show in a list.

  • 写回答

1条回答 默认 最新

  • dra11767 2017-04-18 19:11
    关注

    Your DB should to look something like

    Players

    ID | Player Nickname
    1  | Player1
    2  | Player2
    3  | Player3
    

    Donkey Kong

    PlayerID  | Score  
    Player2ID | 34,000 
    Player1ID | 32,000 
    Player3ID | 29,000 
    

    Robotron

    PlayerID  | Score 
    Player1ID | 39,000
    Player3ID | 32,000
    Player2ID | 21,000
    

    DB should be only used to store data. Rank and awarded points should be moved to the code as those are redundant parts.

    Then query each table with desc sort on score columns. That will create ranked tables with top scorer as #1 etc. That retrieved data store in arrays for each game with Key->Values where Key will be a rank # (1+ incremental) and Values can be other array with stored players data (after join) and a score or a string something like PlayerX | Score# if you need a score data for anything because for real you need only Players data sort on scores per game.

    After that you need to loop through player table and create an array of players where you will store tournament points retrieved from looping each game array and decrease tournament points on each rank and assign to a proper player.

    Hope it helps

    PS. For existing data I will create views something like

    set @max_points=1000;
    select Rank, Player, 
    @current_points := IF(Rank = 1, @max_points, @current_points-1) AS points
    from DonkeyKong
    

    for each game

    then do final view to sum all tournaments points

    select dk.Player AS Player,(dk.Points + ro.Points) AS Total 
    from RO_view AS ro 
    left join DK_view AS dk 
    on dk.Player = ro.Player
    
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题