SQL如何查询一张表里的重复字段
比如我有个表
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)
