选择MySQL中出现最多的值

如果您希望每个线程的计数,我认为您可以使用嵌套查询;先按线程分组,然后按用户分组:

SELECT thread_id AS tid,
    (SELECT user_id FROM thread_posts 
        WHERE thread_id = tid 
        GROUP BY user_id
        ORDER BY COUNT(*) DESC
        LIMIT 0,1) AS topUser
FROM thread_posts
GROUP BY thread_id

这将列出每个线程的用户ID出现情况。

SELECT thread_id, user_id, COUNT(*) as postings
FROM thread_posts
GROUP BY thread_id, user_id

但是您只希望为每个线程选择顶级用户

SELECT thread_id, user_id, postings
FROM (
  SELECT thread_id, user_id, COUNT(*) as postings
  FROM thread_posts
  GROUP BY thread_id, user_id
)
HAVING postings = max(postings)

相当于

SELECT thread_id, user_id, COUNT(*) as postings
FROM thread_posts
GROUP BY thread_id, user_id
HAVING postings = max(postings)

HAVING关键字通常与聚合操作一起使用,以挑选满足HAVING子句条件的聚合输出行。

HAVING子句不同于WHERE子句,其中HAVING子句过滤查询的结果输出。然而,WHERE子句过滤查询的输入数据。 由于HAVING子句过滤查询的结果输出,因此它必须出现在ORDER BY和GROUP BY子句之后。

如果您在“每组最多N个”标签下检查问题,会有很多例子。但是在这种情况下,您不需要定义如何处理关系——如果两个或更多的用户具有相同的计数值会怎么样?

SELECT DISTINCT
       tp.thread_id,
       tp.user_id
  FROM THREAD_POSTS tp
  JOIN (SELECT t.thread_id,
               t.user_id,
               COUNT(t.user_id) AS occurrence,
               CASE
                 WHEN @thread != t.thread_id THEN @rownum := 1
                 ELSE @rownum := @rownum + 1
               END AS rank,
               @thread := t.thread_id
          FROM THREAD_POSTS t
          JOIN (SELECT @rownum := 0, @thread := -1) r
      GROUP BY t.thread_id, t.user_id
      ORDER BY t.thread_id, occurrence DESC) x ON x.thread_id = tp.thread_id
                                              AND x.user_id = tp.user_id
                                              AND x.rank = 1