如何 找出表中两个字段重复(同单位同名)的记录

[sh5281]

如下表:person
PersonName, PersonDept, PersonType, ....

如何将表中 相同单位，而且姓名相同的人员 记录给搜索出来。

[zlp321002]

select
*
from 表 A
where exists
(select 1 from 表 where PersonName=A.PersonName and PersonDept=A.PersonDept)

[Andy__Huang]

select PersonName, PersonDept,num=count(*)
from person
group by PersonName, PersonDept
having count(*)>1

[zlp321002]

--上面有点问题，没有标识唯一主键，这样试下/

select * from @t A
where
(select count(*) from @t where a=A.a and b=A.b)>1

[love16]

select 别名，count(别名) from (select (rtrim(PersonName)+rtrim(PersonDept)) as 别名 from 表名) group by 别名 having count(别名)>2

[zlp321002]

->2楼还真快，我说我自己。：）

[love16]

select 别名，count(别名) from (select (rtrim(PersonName)+rtrim(PersonDept)) as 别名 from 表名) group by 别名 having count(别名)>1

[点点星灯]

--没测试

select a.* from person a,person
where a.PersonDept=PersonDept and a.PersonName=PersonName

[love16]

create table aa (PersonName varchar(20), PersonDept varchar(20), PersonType varchar(20))

insert into aa
select '李四','财务','11'union all
select '李三','采购','12'union all
select '李四','财务','13'union all
select '李五','财务','14'union all
select '李一','采购','15'union all
select '李三','采购','16'

select 别名,count(别名) 重复个数 from
(select (rtrim(PersonName)+rtrim(PersonDept)) as 别名 from aa) t group by 别名 having count(别名)>1

结果：

李三采购 2
李四财务 2

[yongwin]

select *,distinct(PersonName,PersonDept) from person a left join person
on a.PersonName=PersonName where a.PersonDept=PersonDept

[sh5281]

好了，多谢楼上各位的答复，多谢了。

[funsuzhou]

select a.* from person a
right join (select PersonName,PersonDept from person group by PersonName,PersonDept having count(*)>1)b
on a.PersonName=b.PersonName and a.PersonDept=b.PersonDept