PostgreSQL -选择多个最大计数
PostgreSQL -选择多个最大计数
提问于 2015-10-21 13:25:49
我在用PostgreSQL。
我有一个桌子上的竞争对手，有关于一个电子竞技比赛的竞争对手的信息.该表包含gameID、竞争对手的昵称以及他/她所扮演的角色的名称。
我想选择每一个尼克和角色，他们扮演的最多。
例如，如果竞争对手Faker已经扮演角色Ryze 4次和Lulu 3次，我希望Faker Ryze 4在输出中。
到目前为止，这就是我所拥有的：
select nick, character, count(*) as played
from competitor
group by nick, character
order by nick;但是在输出中，我得到了Faker Ryze 4和Faker Lulu 3。
我试过max()和嵌套，但我想不出来。
回答 3
Stack Overflow用户
回答已采纳
发布于 2015-10-21 14:44:17
试试这个：
with counts as (
select nick, character, count(*) cnt, max(sometimestamp) hora
from competitor
group by nick, character
order by nick, cnt desc, hora
)
select nick, character, max(cnt)
from counts c1 where nick
in (select nick from counts where nick = c1.nick limit 1)
and character in (select character from counts where nick = c1.nick limit 1)
group by nick, character
order by nickmax(sometimestamp) hora将是平局，你将不得不适应。或者，如果你不关心领带，就移除这个领域。这个查询将给出每个缺口的字符，并使用更大的计数，如果是领带，它将返回第一个到达最大计数的字符。
Stack Overflow用户
发布于 2015-10-21 14:38:09
这就行了。
SELECT nick, character,played
FROM
(SELECT s1.*,
row_number() OVER (PARTITION BY nick
ORDER BY played DESC) AS row1
FROM
(SELECT nick, character, COUNT(*) AS played
FROM competitor
GROUP BY nick, character
ORDER BY nick,
COUNT(*) DESC) s1)s2
WHERE row1=1;查询是100%正确的。SQL 这里。此外，它也是唯一能够轻松支持相同尼克(通过用rank()更改row_number() )并避免不必要的JOIN的查询。
Stack Overflow用户
发布于 2015-10-21 13:55:48
您正在使用max和嵌套沿着正确的路线前进，我已经稍微修改了给这里的答案
select t1.nick, t1.character, t1.count
from (
select nick, character, count(*) as played
from competitor
group by nick, character
) t1
inner join
(
select nick, max(played) max_count
from
(
select nick, character, count(*) as played
from competitor
group by nick, character
)
group by nick
) t2
on t1.nick = t2.nick
and t1.count = t2.max_count如果您为基本查询创建了一个视图，这可能更容易阅读：
称为PlayerCount的视图：
select nick, character, count(*) as played
from competitor
group by nick, character现在，SQL查询是：
select t1.nick, t1.character, t1.count
from PlayerCount t1
inner join
(
select nick, max(played) max_count
from PlayerCount
group by nick
) t2
on t1.nick = t2.nick
and t1.count = t2.max_count页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33260584
复制相关文章
相似问题
腾讯云开发者
