我有一张桌子
create table test(id int not null primary key, day date not null);
insert into test(id, day) values(1, '2006-10-08');
insert into test(id, day) values(2, '2006-10-08');
insert into test(id, day) values(3, '2006-10-09');
select * from test;
+----+------------+
| id | day |
+----+------------+
| 1 | 2006-10-08 |
| 2 | 2006-10-08 |
| 3 | 2006-10-09 |
+----+------------+
select day, count(*) from test GROUP BY day;
+------------+----------+
| day | count(*) |
+------------+----------+
| 2006-10-08 | 2 |
| 2006-10-09 | 1 |
+------------+----------+
select day, count(*) from test group by day HAVING count(*) > 1;
+------------+----------+
| day | count(*) |
+------------+----------+
| 2006-10-08 | 2 |
+------------+----------+
我需要的是，我需要返回重复的条目
这是我需要的简单输出
+------------+----------+
| day | id |
+------------+----------+
| 2006-10-08 | 2 |
| 2006-10-08 | 1 |
+------------+----------+
最佳答案
尝试自我加入
SELECT T1.day, T1.id
FROM test T1
INNER JOIN test T2
ON T1.id <> T2.id AND T1.day = T2.day
关于sql - 如何Sql仅返回所有重复的条目，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/1259370/