查詢/刪除資料庫中重複資料

比如說我有一張表位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)