如何选择具有最大计数的行进行分组 [英] How do I select a row with max count doing a group by

问题描述

我有一个表 posts，其中包含列(id、user_name、thread_id).
用户可以为一个主题提交多个帖子.要发布的线程是一对多的.

I have a table posts with columns (id, user_name, thread_id).
A user can submit multiple posts for a thread. thread to post is one to many.

我需要找出谁提交了每个线程的最大帖子数.所以结果是Max(Count), user_name, thread_id 其中每个 thread_id 只有一行.

I need to find out who submitted max posts per thread. So the result would be Max(Count), user_name, thread_id WHERE there will be only one row per thread_id.

表太大，所以我想尽可能优化查询.

The table is too huge so I wanted to get the query optimized as much as I could.

推荐答案

您可以尝试使用 group by 和 sharing 子句:

You can try with the group by and having clauses:

select t.user_name, t.thread_id , count(*) as max_count
from tbl t
group by t.user_name, t.thread_id
having count(*) = ( select count(*) as ttl
                    from tbl
                    where thread_id = t.thread_id
                    group by user_name
                    order by ttl desc
                    limit 1 )

这篇关于如何选择具有最大计数的行进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！