比如我有个表
create table tb
(
username varchar(20),
userpwd varchar(20)
)
insert into tb values( '1 ', 'a ')
insert into tb values( '1 ', 'aa ')
insert into tb values( '1 ', 'aaa ')
insert into tb values( '1 ', 'aaaa ')
insert into tb values( '1 ', 'aaaaa ')
insert into tb values( '1 ', 'aaaaaa ')
insert into tb values( '1 ', 'aaaaaaa ')
insert into tb values( '1 ', 'aaaaaaaa ')
insert into tb values( '1 ', 'aaaaaaaaa ')
insert into tb values( '1 ', 'aaaaaaaaaa ')
insert into tb values( '1 ', 'aaaaaaaaaaa ')
insert into tb values( '1 ', 'aaaaaaaaaaaa ')
insert into tb values( '1 ', 'aaaaaaaaaaaaa ')
insert into tb values( '2 ', 'b ')
insert into tb values( '3 ', 'c ')
insert into tb values( '4 ', 'd ')
insert into tb values( '5 ', 'e ')
insert into tb values( '6 ', 'f ')
insert into tb values( '7 ', 'a ')
insert into tb values( '7 ', 'aa ')
insert into tb values( '7 ', 'aaa ')
insert into tb values( '7 ', 'aaaa ')
insert into tb values( '7 ', 'aaaaa ')
insert into tb values( '7 ', 'aaaaaa ')
insert into tb values( '7 ', 'aaaaaaa ')
insert into tb values( '7 ', 'aaaaaaaa ')
insert into tb values( '7 ', 'aaaaaaaaa ')
insert into tb values( '7 ', 'aaaaaaaaaa ')
insert into tb values( '7 ', 'aaaaaaaaaaa ')
insert into tb values( '7 ', 'aaaaaaaaaaaa ')
insert into tb values( '7 ', 'aaaaaaaaaaaaa ')
这里面 '1 ' 和 '7 '都有重复,如何查询出来呢?我需要查询重复的,不重复的不要,象 '2 ', '3 ', '4 ', '5 ', '6 '都不要
------解决方案--------------------------------------------------------
select * from tb where username in (select username from tb group by username having count(username)> 1)