mysql - 如何从表格中找到最重复的元素？

Question

我有一个表 tbl_user，我想找出表中哪些 user_name 具有最多的重复值。我只想检索那些 user_id(s)。

我还需要一个单独的查询来查找表中重复 user_name(s) 的计数。

Answer 1

第一个查询找到最大数量的重复用户名:

  SELECT user_name, COUNT(user_id) AS amount
    FROM tbl_user
GROUP BY user_name
ORDER BY amount DESC

然后获取 id`s:

# faster way using username from last query

  SELECT user_id
    FROM tbl_user
   WHERE user_name = '$user_name'


# longer way

  SELECT user_id
    FROM tbl_user
   WHERE user_name = (
       SELECT user_name
         FROM tbl_user
     GROUP BY user_name
     ORDER BY COUNT(amount) DESC
        LIMIT 1
   )