用最少的时间获得最高的用户分数 [英] 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
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
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
OUTPUT
╔═════════╦═════════╦══════════╦═════════╗
║ GAME_ID ║ USER_ID ║ SUMPOINT ║ SUMTIME ║
╠═════════╬═════════╬══════════╬═════════╣
║ 2 ║ 1 ║ 2 ║ 30 ║
║ 3 ║ 2 ║ 0 ║ 30 ║
╚═════════╩═════════╩══════════╩═════════╝
这篇关于用最少的时间获得最高的用户分数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!