使用Query DSL从每个组中选择具有最大值的记录 [英] Select record with max value from each group with Query DSL
问题描述
我有一个得分表,其中有球员得分,我想为每个得分最高的球员选择唯一的记录.
I have a score table where I have players scores, and I want select unique records for each player with the biggest score.
这是桌子:
id | player_id | score | ...
1 | 1 | 10 | ...
2 | 2 | 21 | ...
3 | 3 | 9 | ...
4 | 1 | 30 | ...
5 | 3 | 2 | ...
预期结果:
id | player_id | score | ...
2 | 2 | 21 | ...
3 | 3 | 9 | ...
4 | 1 | 30 | ...
我可以这样用纯SQL来实现:
I can achieve that with pure SQL like this:
SELECT *
FROM player_score ps
WHERE ps.score =
(
SELECT max(ps2.score)
FROM player_score ps2
WHERE ps2.player_id = ps.player_id
)
您能告诉我如何使用查询dsl实现相同的查询吗?我找到了一些使用JPASubQuery的解决方案,但该类对我不起作用(我的IDE无法解析该类).我正在使用querydsl4.x.预先谢谢你.
Can you tell me how to achieve the same query with query dsl? I found some solutions with JPASubQuery but this class doesn't work for me (my IDE cannot resolve this class). I am using querydsl 4.x. Thank you in advance.
推荐答案
JPASubQuery
已在querydsl 4中删除.请改为使用JPAExpressions.select
.您的WHERE
子句应如下所示:
JPASubQuery
has been removed in querydsl 4. Instead use JPAExpressions.select
. Your WHERE
clause should look something like this:
.where(playerScore.score.eq(JPAExpressions.select(playerScore2.score.max())
.from(playerScore2))
.where(playerScore2.playerId.eq(playerScore.playerId)))
这篇关于使用Query DSL从每个组中选择具有最大值的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!