Получите максимальные оценки пользователей с минимальными временными суммами

Question

У меня есть таблицы MySql, которые выглядят так

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

И мне нужно запросить его, чтобы он суммировал очки и время за игру, чтобы это выглядело так

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-запроса? заранее спасибо

Answer 1

Этот запрос ниже даст вам частичный результат.

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

ВЫХОД

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

поскольку MySQL не поддерживает функции Windows, как и любая другая СУБД, это может привести к желаемому результату.

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

ВЫХОД

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

Answer 2

Ответ JW по сути такой же, как мой. Я просто думаю, что привлечение пользователя так рано, кажется, излишне усложняет ситуацию. Как насчет...

SELECT x.*
     , u.user_id
  FROM 
     ( SELECT game_id
            , SUM(score) ttl_score
            , SUM(time) ttl_time 
         FROM scores 
        GROUP 
           BY game_id
     ) x
  JOIN
     ( SELECT ttl_score
            , MIN(ttl_time) min_ttl_time
         FROM
            ( SELECT game_id
                   , SUM(score) ttl_score
                   , SUM(time) ttl_time 
                FROM scores 
               GROUP 
                  BY game_id 
            ) a
        GROUP
           BY ttl_score
     ) y
    ON y.ttl_score = x.ttl_score
   AND y.min_ttl_time = x.ttl_time
  JOIN games g
    ON g.game_id = x.game_id;