用最少的时间获得最高的用户分数 [英] Get max user scores with min time sums

问题描述

我的MySql表看起来像这样

 分数
 game_id | level |分数|时间
 -------- + ------- + ------- + ----- 
 1 | 1 | 1 | 10 
 1 | 2 | 0 | 10 
 1 | 3 | 1 | 20 
 2 | 1 | 1 | 5 
 2 | 2 | 1 | 15 
 2 | 3 | 0 | 10 
 3 | 1 | 0 | 10 
 3 | 2 | 0 | 10 
 3 | 3 | 0 | 10 
 
游戏
 game_id | user_id 
 -------- + -------- 
 1 | 1 
 2 | 1 
 3 | 2 
  

我需要查询它，因此它每场比赛总结点数和时间，所以它看起来像这样

  game_id | user_id | sumPoints | sumTime 
 -------- + --------- + ----------- + -------- 
 1 | 1 | 2 | 40 
 2 | 1 | 2 | 30 
 3 | 2 | 0 | 30 
  

比我需要为每个用户获得最好的分数（最好的分数我的意思是max（sumPoints ）和min（sumTime），所以它看起来像这样：

  game_id | user_id | sumPoints | sumTime 
  - ------ + --------- + ----------- + -------- 
 2 | 1 | 2 | 30 
 3 | 2 | 0 | 30 
  

这就是最终结果。
我想知道，我怎么能通过SQL查询做到这一点？
在此先感谢

 解决方案

SELECT a.game_ID，
b.user_id，
SUM（a.Score）sumPoint，
SUM（a.time）sumTime
FROM scores a
INNER JOIN游戏b
ON a.game_ID = b.game_ID
GROUP BY a.game_ID，b.user_id


• SQLFiddle演示





OUTPUT

 ╔══════ ════════════════════════════════════
║GAME_ID║USER_ID║SUMPOINT║SUMTIME║
╠═══════════════════$ b $b║1║ 1║2║40║
║2║1║2║30║
║3║2║0║30║
╚═════════════════ ════════════════
  

由于 MySQL 不像其他RDBMS一样支持Windows函数，所以产生您想要的结果可能会很麻烦。

  SELECT a。* 
 FRO M 
（
 SELECT a.game_ID，
 b.user_id，
 SUM（a.Score）sumPoint，
 SUM（a.time）sumTime 
 FROM scores a 
 INNER JOIN游戏b 
 ON a.game_ID = b.game_ID 
 GROUP BY a.game_ID，b.user_id 
）a 
 INNER JOIN 
（
 SELECT user_ID，
 MIN（sumTime）sumTime 
 FROM 
（
 SELECT a.game_ID，
 b.user_id，
 SUM（a.Score）sumPoint，
 SUM（a.time）sumTime 
 FROM scores a 
 INNER JOIN游戏b 
 ON a.game_ID = b.game_ID 
 GROUP BY a.game_ID，b.user_id 
 ）s 
 GROUP BY user_ID 
）b ON a.user_id = b.user_id AND 
 a.sumTime = b.sumTime 
  







• SQLFiddle演示






OUTPUT

 ╔════════════════════════════════════ ║GAME_ID║USER_ID║SUMPOINT║SUMTIME║
╠══════════════════════════════════════ ═════╣$ b $b║2║1║2║30║$ b $b║3║2║0║30║$ b $b╚════════════ ════════════════
  




I have MySql tables that look like this


scores
game_id | level | score | time
--------+-------+-------+-----
1       | 1     | 1     | 10
1       | 2     | 0     | 10
1       | 3     | 1     | 20
2       | 1     | 1     | 5
2       | 2     | 1     | 15
2       | 3     | 0     | 10
3       | 1     | 0     | 10
3       | 2     | 0     | 10
3       | 3     | 0     | 10

games
game_id | user_id
--------+--------
1       | 1
2       | 1
3       | 2

And i need to query it so it sums points and time per game, so it looks like this

game_id | user_id | sumPoints | sumTime
--------+---------+-----------+--------
1       | 1       | 2         | 40
2       | 1       | 2         | 30
3       | 2       | 0         | 30

And than i need to get best scores for each user (best score i mean where max(sumPoints) and min(sumTime), so it looks like this:

game_id | user_id | sumPoints | sumTime
--------+---------+-----------+--------
2       | 1       | 2         | 30
3       | 2       | 0         | 30

Thats the final result. I can do part where points and time are summing, but i have no idea how can i get tops scores per user. I wonder, how can i do this by SQL query? Thanks in advance

解决方案

This query below will give you the partial result.

SELECT  a.game_ID, 
        b.user_id,
        SUM(a.Score) sumPoint, 
        SUM(a.time) sumTime
FROM    scores a
        INNER JOIN games b
            ON a.game_ID = b.game_ID
GROUP   BY a.game_ID, b.user_id

• SQLFiddle Demo

OUTPUT

╔═════════╦═════════╦══════════╦═════════╗
║ GAME_ID ║ USER_ID ║ SUMPOINT ║ SUMTIME ║
╠═════════╬═════════╬══════════╬═════════╣
║       1 ║       1 ║        2 ║      40 ║
║       2 ║       1 ║        2 ║      30 ║
║       3 ║       2 ║        0 ║      30 ║
╚═════════╩═════════╩══════════╩═════════╝

Since MySQL doesn't support windows functions as like any other RDBMS, this might be messy to produce your desired result.

SELECT  a.*
FROM
        (
            SELECT  a.game_ID, 
                    b.user_id,
                    SUM(a.Score) sumPoint, 
                    SUM(a.time) sumTime
            FROM    scores a
                    INNER JOIN games b
                        ON a.game_ID = b.game_ID
            GROUP   BY a.game_ID, b.user_id
        ) a
        INNER JOIN
        (
            SELECT  user_ID,
                    MIN(sumTime) sumTime
            FROM
                    (
                        SELECT  a.game_ID, 
                                b.user_id,
                                SUM(a.Score) sumPoint, 
                                SUM(a.time) sumTime
                        FROM    scores a
                                INNER JOIN games b
                                    ON a.game_ID = b.game_ID
                        GROUP   BY a.game_ID, b.user_id
                    ) s
            GROUP   BY user_ID
        ) b ON  a.user_id = b.user_id AND
                a.sumTime = b.sumTime

• SQLFiddle Demo

OUTPUT

╔═════════╦═════════╦══════════╦═════════╗
║ GAME_ID ║ USER_ID ║ SUMPOINT ║ SUMTIME ║
╠═════════╬═════════╬══════════╬═════════╣
║       2 ║       1 ║        2 ║      30 ║
║       3 ║       2 ║        0 ║      30 ║
╚═════════╩═════════╩══════════╩═════════╝

这篇关于用最少的时间获得最高的用户分数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！