如
table
id title
1 a
2 a
3 c
4 d
5 e
6 e
7 h
8 h
我想把查询出的重覆值插入到新的表
如下结果
table2
id title
1 a
2 a
5 e
6 e
7 h
8 h
table
id title
1 a
2 a
3 c
4 d
5 e
6 e
7 h
8 h
我想把查询出的重覆值插入到新的表
如下结果
table2
id title
1 a
2 a
5 e
6 e
7 h
8 h
select *
from table1 a
where exists (select id from table1 where title=a.title and id<>a.id);
+------+-------+
| id | title |
+------+-------+
| 1 | a |
| 2 | a |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | e |
| 7 | h |
| 8 | h |
+------+-------+
8 rows in set (0.00 sec)mysql>
mysql> select *
-> from table1 a
-> where exists (select id from table1 where title=a.title and id<>a.id);
+------+-------+
| id | title |
+------+-------+
| 1 | a |
| 2 | a |
| 5 | e |
| 6 | e |
| 7 | h |
| 8 | h |
+------+-------+
6 rows in set (0.00 sec)mysql>