如何一次查找表中重复数据的sql

[chiefangel]

表test
字段
id，name，value

如何用一句sql找出 id和name完全相同的数据？？？？

[LBYYBL]

select id,name from test group by id,name having count(1)>1

[zhangzs8896]

select a.* from test a,
(
select id,name from test
group by id ,name
having count(*)>1
where a.id=b.id and a.name=b.name

[Overriding]

select id,name from test group by id,name having count(id+name)>1

[coeltdit]

select A.* from test A
inner join (select id,name
from test
group by id,name
having count(*)>1) B
on A.ID=B.ID and A.Name=B.name

[zhangzs8896]

少了一点)b

select a.* from test a,
(
select id,name from test
group by id ,name
having count(id)>1 )b
where a.id=b.id and a.name=b.name

[xluzhong]

alter table tablename add sid int identity(1,1)
go
select *
form tablename a
where exists(select 1 from tablename where id=a.id and [name]=a.[name] and sid<a.sid)
go
alter table tablename drop column sid