select PersonName, PersonDept,num=count(*) from person group by PersonName, PersonDept having count(*)>1
--上面有点问题,没有标识唯一主键,这样试下/select * from @t A where (select count(*) from @t where a=A.a and b=A.b)>1
select 别名,count(别名) from (select (rtrim(PersonName)+rtrim(PersonDept)) as 别名 from 表名) group by 别名 having count(别名)>2
--没测试select a.* from person a,person where a.PersonDept=PersonDept and a.PersonName=PersonName
select 别名,count(别名) from (select (rtrim(PersonName)+rtrim(PersonDept)) as 别名 from 表名) group by 别名 having count(别名)>1
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
select *,distinct(PersonName,PersonDept) from person a left join person on a.PersonName=PersonName where a.PersonDept=PersonDept
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
from person
group by PersonName, PersonDept
having count(*)>1
where
(select count(*) from @t where a=A.a and b=A.b)>1
where a.PersonDept=PersonDept and a.PersonName=PersonName
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
on a.PersonName=PersonName where a.PersonDept=PersonDept
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