查找重复的记录(sql2000)

[txt_ly]

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)

有没有更好的办法

[dawugui]

select b,c from tb group by b,c having count(*) > 1

[dawugui]

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

[txt_ly]

to dawugui(潇洒老乌龟)
b,c列任一列重复.都要找出来

[dawugui]

你是查只要B有重复的或只要C有重复的吧?
不是B,C同时重复吧.

[dawugui]

我的第二个语句已经能满足你的要求.
第一个,理解错了.

[txt_ly]

to dawugui(潇洒老乌龟)
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)
跟你的那条sql效益有什么区别吗

[dawugui]

不知道,没有大的数据量,测试不出来.你自己试试

[kk19840210]

比楼主的效率还低些

[tomyuansir]

有
用union all代替 or
不信的话去查网站就知道了

[bluebullet]

老乌龟,有错误了!
GROUP BY A,B,C,....
如果A有多列,但是后面的B,C等没重复记录,那就出不来A的重复记录了!
终于挑到个毛病,嘎嘎

[forever_forest]

我查了一下，貌似效率差不多，都很低

[dawugui]

老乌龟,有错误了!
GROUP BY A,B,C,....
如果A有多列,但是后面的B,C等没重复记录,那就出不来A的重复记录了!
终于挑到个毛病,嘎嘎

-------------------
楼主没说要A有重复的,如果要查,把A加上即可.

[txt_ly]

还有一个问题!
空记录不算重复,第6,7行不能查出来
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')
insert test values('6','','')
insert test values('7','','')
go

)

[zhu19841211]

楼主的我测试了！在一个表中查询所有相同的数据
但是我用DISTINCT 关键字查询的结果和楼主方法的结果不一样
select * from w_hiretruck where cardid in (select cardid from w_hiretruck group by cardid having count(*) >1) 查询出是131个
但是我用
select * from w_hiretruck 8125个
select distinct cardid from w_hiretruck 8063个两个的差是62个不一样啊