查找重复的记录(sql2000)
drop   table   test
go
create   table   test
(
      a     varchar(20),
      b   varchar(20),
      c   varchar(20)
)
go
delete   from   test
go
insert   test   values(   '1 ', 'liuyu ', 'home ')
insert   test   values( '2 ', 'keny ', 'dogse ')
insert   test   values( '3 ', 'keny ', 'cgkkk ')
insert   test   values( '4 ', 'ibm ', 'home ')
insert   test   values( '5 ', 'hxz ', 'kkkkk ')
go

根据b,c列查找重复记录
我用的查询语句是
select   *   from   test   where   b   in   (select   b   from   test   group   by   b   having   count(*)   > 1)   or   c   in   (select   c   from   test   group   by   c   having   count(*)   > 1)  
假如还有d,e列,且要根据其来查找重复记录,则我的查询语句变得很长,效益也差
select   *   from   test   where   b   in   (select   b   from   test   group   by   b   having   count(*)   > 1)   or   c   in   (select   c   from   test   group   by   c   having   count(*)   > 1)  
or   d   in   (select   d   from   test   group   by   d   having   count(*)   > 1)  
or   e   in   (select   e   from   test   group   by   e   having   count(*)   > 1)  

有没有更好的办法


------解决方案--------------------
create table test
(
a varchar(20),
b varchar(20),
c varchar(20)
)
go
delete from test
go
insert test values( '1 ', 'liuyu ', 'home ')
insert test values( '2 ', 'keny ', 'dogse ')
insert test values( '3 ', 'keny ', 'cgkkk ')
insert test values( '4 ', 'ibm ', 'home ')
insert test values( '5 ', 'hxz ', 'kkkkk ')
go

select * from test where b in
(
select b from test group by b having count(*) > 1
)
union
select * from test where c in
(
select c from test group by c having count(*) > 1
)

/*
a b c
-------------------- -------------------- --------------------
1 liuyu home
2 keny dogse
3 keny cgkkk
4 ibm home

（所影响的行数为 4 行）
*/
drop table test
------解决方案--------------------
select b,c from tb group by b,c having count(*) > 1

------解决方案--------------------
比楼主的效率还低些
------解决方案--------------------
有
用union all代替 or
不信的话去查网站就知道了
------解决方案--------------------
老乌龟,有错误了!
GROUP BY A,B,C,....
如果A有多列,但是后面的B,C等没重复记录,那就出不来A的重复记录了!
终于挑到个毛病,嘎嘎
------解决方案--------------------
我查了一下，貌似效率差不多，都很低