MySQL 如何选择非“唯一”行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4519582/
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
How to select non "unique" rows
提问by AdrienG
I have the following table, from which i have to fetch non unique rows
我有下表，我必须从中获取非唯一行
+------+------+------+
| id | idA |infos |
+----- +------+------+
| 0 | 201 | 1899 |
| 1 | 205 | 1955 |
| 2 | 207 | 1955 |
| 3 | 201 | 1959 |
+------+------+------+
I'd like fetch all the rows for the column infos, that have a same idAvalue in at least two rows.
我想获取 column 的所有行infos，这些idA行在至少两行中具有相同的值。
Output of the query for the above table must be
上表查询的输出必须是
infos
1899
1959
I've tried the following requests with no success :
我尝试了以下请求但没有成功：
SELECT idA FROM XXX WHERE NOT EXISTS(SELECT * FROM XXX GROUP BY idA)SELECT * FROM XXX a WHERE NOT EXISTS(SELECT * FROM XXX b WHERE a.RVT_ID=b.RVT_ID GROUP BY idA)
SELECT idA FROM XXX WHERE NOT EXISTS(SELECT * FROM XXX GROUP BY idA)SELECT * FROM XXX a WHERE NOT EXISTS(SELECT * FROM XXX b WHERE a.RVT_ID=b.RVT_ID GROUP BY idA)
回答by Mark Byers
Try this:
尝试这个：
SELECT T1.idA, T1.infos
FROM XXX T1
JOIN
(
SELECT idA
FROM XXX
GROUP BY idA
HAVING COUNT(*) >= 2
) T2
ON T1.idA = T2.idA
The result for the data you posted:
您发布的数据的结果：
idaA infos 201 1899 201 1959
回答by judda
Something like this should work:
这样的事情应该工作：
SELECT idA, COUNT(*) FROM XXX GROUP BY idA HAVING COUNT(*) > 1
回答by Mchipouras
SELECT id, idA, COUNT(infos) AS cnt
FROM mytable
GROUP BY infos
HAVING cnt > 1
回答by user2292112
This is probably what you are looking for:
这可能是您正在寻找的：
SELECT *, COUNT(DISTINCT infos) FROM table GROUP BY idA HAVING COUNT(DISTINCT infos) > 1;
回答by Vincent Mimoun-Prat
This should give all the rows where "infos" appear exactly once (Tested on MySQL)
这应该给出“信息”只出现一次的所有行（在 MySQL 上测试）
SELECT id, idA, count(infos) AS cnt
FROM mytable
GROUP BY infos
HAVING cnt=1
Data
数据
id ida infos
1 201 1955
2 202 1978
3 203 1978
4 204 1956
5 0 1980
Result
结果
id idA cnt
1 201 1
4 204 1
5 0 1
