php 显示 MySQL 中包含相同值的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3797799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Show all rows in MySQL that contain the same value
提问by Simon
I have a MySQL database:
我有一个 MySQL 数据库：
ID | Name
1 | Bob
2 | James
3 | Hyman
4 | Bob
5 | James
How would I return a list of all the columns where the same name appears more than once, eg, I'd like to return this:
我将如何返回同名出现不止一次的所有列的列表，例如，我想返回这个：
1 | Bob
2 | James
4 | Bob
5 | James
I've written a count query:
我写了一个计数查询：
SELECT Name, COUNT(Name)
AS NumOccurrences
FROM table
GROUP BY Name
HAVING ( COUNT(Name) > 1 )
But that just returns something like this:
但这只会返回如下内容：
Bob | 2
James | 2
Whereas I want to return the full rows returned.
而我想返回返回的完整行。
Any help would be greatly appreciated, thanks.
任何帮助将不胜感激，谢谢。
采纳答案by Juha Syrj?l?
Try this sql query:
试试这个 sql 查询：
select distinct a.name, a.id
from table a, table b
where a.name = b.name and a.id != b.id
回答by MightyE
You can do it with a sub select
您可以使用子选择来完成
SELECT * FROM table WHERE Name IN (
SELECT Name FROM table GROUP BY Name HAVING count(*) > 1
)
Also if your distinction match is multiple columns, you can use cartesian sets:
此外，如果您的区分匹配是多列，则可以使用笛卡尔集：
SELECT * FROM table WHERE (firstName, lastName) IN (
SELECT firstName, lastName FROM table GROUP BY firstName, lastName HAVING count(*) > 1
)
回答by Pramendra Gupta
Try this
尝试这个
SELECT Name, COUNT(Name) AS NumOccurrences
FROM table
GROUP BY Name
HAVING COUNT(*) > 0
