选择MySQL中最常出现的值 [英] Select most occurring value in MySQL

问题描述

我正在寻找一种方法来选择最常出现的值，例如每个线程发布最多的人;

pre $ SELECT MOST_OCCURRING（user_id）FROM thread_posts GROUP BY thread_id


解决方案

如果你想在每个线程的基础上计数，我认为你可以使用嵌套查询;按线程先分组然后按用户分组：



  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 
  




I'm looking for a way to select the most occurring value, e.g. the person who posted most for each thread;

SELECT MOST_OCCURRING(user_id) FROM thread_posts GROUP BY thread_id

Is there a good way to do this?

解决方案

If you want a count on a per thread basis, I think you can use a nested query; grouping by thread first and then by user:

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

这篇关于选择MySQL中最常出现的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！