我有一个看起来像这样的表...
user_id, match_id, points_won
1 14 10
1 8 12
1 12 80
2 8 10
3 14 20
3 2 25
我想编写一个 MYSQL 脚本,该脚本可以拉回用户在单场比赛中赢得的最多积分,并将match_id包含在结果中 - 换句话说......
user_id, match_id, max_points_won
1 12 80
2 8 10
3 2 25
当然,如果我不需要match_id我可以做......
select user_id, max(points_won)
from table
group by user_id
但是,一旦我将match_id添加到"选择"和"分组依据"中,我就会为每个匹配项添加一行,如果我只将match_id添加到"选择"(而不是"分组依据"(,那么它将无法正确关联points_won。
理想情况下,我也不想执行以下操作,因为它感觉不是特别安全(例如,如果用户在多场比赛中赢得了相同数量的积分(......
SELECT t.user_id, max(t.points_won) max_points_won
, (select t2.match_id
from table t2
where t2.user_id = t.user_id
and t2.points_won = max_points_won) as 'match_of_points_maximum'
FROM table t
GROUP BY t.user_id
这个问题还有更优雅的选择吗?
这比在MySQL中需要的要难。 一种方法有点黑客,但在大多数情况下都有效。 这就是group_concat()
/substring_index()
技巧:
select user_id, max(points_won),
substring_index(group_concat(match_id order by points_won desc), ',', 1)
from table
group by user_id;
group_concat()
将所有match_id
连接在一起,按点降序排列。 然后substring_index()
拿第一个。
两个重要的警告:
- 生成的表达式具有字符串类型,而不考虑内部类型。
group_concat()
使用内部缓冲区,默认情况下,其长度为 1,024 个字符。 可以更改此默认长度。
您可以使用查询:
select user_id, max(points_won)
from table
group by user_id
作为派生表。将其连接到原始表可以得到您想要的内容:
select t1.user_id, t1.match_id, t2.max_points_won
from table as t1
join (
select user_id, max(points_won) as max_points_won
from table
group by user_id
) as t2 on t1.user_id = t2.user_id and t1.points_won = t2.max_points_won
您可以通过在内部查询中添加限制 1 来优化查询。
SELECT t.user_id, max(t.points_won) max_points_won
, (select t2.match_id
from table t2
where t2.user_id = t.user_id
and t2.points_won = max_points_won limit 1) as 'match_of_points_maximum'
FROM table t
GROUP BY t.user_id
编辑:仅适用于postgresql,sql-server,oracle
您可以使用row_number:
SELECT USER_ID, MATCH_ID, POINTS_WON
FROM
(
SELECT user_id, match_id, points_won, row_number() over (partition by user_id order by points_won desc) rn
from table
) q
where q.rn = 1
对于类似的函数,请查看Gordon Linoff的答案或本文。
在您的示例中,您对每个用户的结果集进行分区,然后按 points_won desc 排序以首先获得最高获胜点。