go
--id相同 其他字段也相同（一条记录重复录入了）
select id,name,addrss from table11 group by id,name,addrss having count(*)> 1
------解决方案----------------------drop table t,#t
create table t(id int, name varchar(200),address varchar(200))
insert t select 1 , 'huang ' , 'wuhan '
union all select 1 , 'huang ' , 'wuhan '
union all select 2 , 'li ' , 'beijing '
union all select 2 , 'zhang ' , 'nanjing '
union all select 2 , 'hu ' , 'guangzhou '
union all select 3 , 'wang ' , 'shenzhen '
select id1=identity(int,1,1), * into #t from t
--分别查出ID相同其他字段不同 （几个人ID重复了）
select id,name,address from #t a where exists( select 1 from #t b
where a.name=b.name and a.address=b.address and a.id=b.id and b.id1> a.id1)
---id相同 其他字段也相同（一条记录重复录入了）
select id,name,address from #t a where exists( select 1 from #t b
where a.name!=b.name and a.address!=b.address and a.id=b.id
)
drop table t,#t
------解决方案--------------------create table T(id int, name varchar(10), addrss varchar(10))
insert T select 1, 'huang ', 'wuhan '
union all select 1, 'huang ', 'wuhan '
union all select 2, 'li ', 'beijing '
union all select 2, 'zhang ', 'nanjing '
union all select 2, 'hu ', 'guangzhou '
union all select 3, 'wang ', 'shenzhen '
--1
select * from T tmp
where (select count(*) from T where id=tmp.id and name <> tmp.name and addrss <> tmp.addrss)> 1
--result
id name addrss
----------- ---------- ----------
2 li beijing
2 zhang nanjing
2 hu guangzhou
(3 row(s) affected)
--2
select id, name, addrss from T
group by id, name, addrss
having count(*)> 1
--result
id name addrss
----------- ---------- ----------
1 huang wuhan
(1 row(s) affected)
