查询/删除数据库中重复数据

比如说我有一张表位UnitParametInfo，里面有三个字段，ID自增字段，departmentid部门标识，parameterid另外的标识字段

 

查询重复数据

select count(DepartmentId), max(ID)
from UnitParametInfo where ParameterID=109
group by DepartmentId
having count(DepartmentId) >1

 

删除重复数据，只保留一条

delete from UnitParametInfo
where ParameterID=109 and DepartmentId  in (select  DepartmentId  from UnitParametInfo
where ParameterID=109
group  by  DepartmentId   having  count(DepartmentId) > 1)
and ID not in (select min(ID) from 
UnitParametInfo where ParameterID=109  group by DepartmentId  having count(DepartmentId )>1)