如何编写mysql5查询以从表中查找重复的行?

Hi I have a table like this

嗨，我有一张像这样的桌子

ID   UserName
1     test@test.com
2     test@test.com
3     john@stack.com
4     test@test.com
5     adam@stack.com
6     john@stack.com

I need an output like this. I need only repeated rows list. How can I create this kind of an output using mysql query.

我需要这样的输出。我只需要重复行列表。如何使用mysql查询创建这种输出。

ID   UserName           Count
1     test@test.com       3
2     john@stack.com      2

Please help me. Thanks.

请帮助我。谢谢。

5 个解决方案

#1

since the ID is not unique so its a bit not logical to get the sum of unique UserName from the table.

由于ID不是唯一的，所以从表中获取唯一用户名的总和是不符合逻辑的。

If the ID is not required we can get the result from single query.

如果不需要ID，我们可以从单个查询获得结果。

SELECT UserName, COUNT(UserName) AS Count
FROM TableName GROUP BY UserName
HAVING COUNT(UserName) > 1;

But in the case of ID in the result it will be a more complicated query including sub-query and inner table.

但是对于结果中的ID，它将是一个更复杂的查询，包括子查询和内部表。

#2

I had the same problem some time ago and solved it like this (as far as I remember):

前段时间我遇到了同样的问题，像这样解决了(据我记忆):

SELECT *
FROM tableA INNER JOIN
(SELECT DISTINCT MAX(id) as id, type_id, temp FROM tableA GROUP BY type_id, temp) AS t
ON tableA.id = t.id
AND tableA.type_id = t.type_id
AND tableA.temp = t.temp

You join the table with itself selecting the ids that are duplicate. The fields that should be tested against duplicate values are in this case type_id and temp. If you need more or less fields that should be considered as duplicates you can adjust the fields.

您通过选择重复的id来连接表。应该针对重复值进行测试的字段在这个例子中是type_id和temp。

I don't know if this helps in your case and if it can be done in a more simple way, so I'm prepared for downvotes ;-)

我不知道这对你是否有帮助，也不知道是否可以用更简单的方法来做，所以我准备投反对票;

Edit: removed last condition AND tableA.id < t.id as suggested by ypercube because it leads to 0 results.

编辑:删除最后一个条件和表a。id < t。正如ypercube建议的id，因为它会导致0个结果。

#3

It looks like you're trying to pull the following data:

看起来你是在试图提取以下数据:

First ID for a given UserName
给定用户名的第一个ID
The UserName itself
用户名本身
The total number of IDs for that UserName
该用户名的id总数

This query should do the trick:

这个查询应该具有以下功能:

SELECT
  MIN(id),
  UserName,
  COUNT(id)
FROM users
GROUP BY UserName;

#4

SELECT UserName
     , COUNT(*) AS `Count` 
FROM tableX
GROUP BY UserName
HAVING COUNT(*) > 1

#5

Hi this is the right answer.

嗨，这是正确答案。

SELECT UserName, COUNT(UserName) AS Count
FROM TableName GROUP BY UserName
HAVING COUNT(UserName) > 1;

#1