mysql - 如何选择重复的行(与不同的行相反)？

Question

我的表中有一个名为 application_id 的列，它包含类似

的值

32,
65,
75,
75,
32,
45,
56,
78,
64

我只想获取重复的值

喜欢 75,32

我认为它与 distinct 有点相反。不知道怎么办？

Answer 1

如果您只想选择 application_id，那么只需使用 @Sayonji 已经建议的查询即可

SELECT application_id FROM MyTable GROUP BY application_id HAVING count(*) > 1

但是如果你想选择所有的列，那么你可以使用子查询来实现:

SELECT DISTINCT * FROM MyTable
WHERE application_id IN
(
   SELECT application_id FROM MyTable
   GROUP BY application_id
   HAVING COUNT(*) > 1
);

或者使用自连接:

SELECT DISTINCT t1.* FROM MyTable t1
JOIN
(
   SELECT application_id FROM MyTable
   GROUP BY application_id
   HAVING COUNT(*) > 1
) t2
ON t1.application_id = t2.application_id;

参见this SQLFiddle