1.sql 查询和删除多条字段的重复语句

查询

select a.* from Base_UserDeptRole a inner join(
   select DeptRoleId,UserId  from Base_UserDeptRole(表)
   group by DeptRoleId,UserId
   having count(*)>1) tem on tem.UserId=a.UserId and tem.DeptRoleId=a.DeptRoleId

删除

select distinct DeptRoleId,UserId into #Tmp from Base_UserDeptRole（表）
delete  Base_UserDeptRole
insert into Base_UserDeptRole (
Id,DeptRoleId,UserId,CreatedOn,CreatedById,ModifiedOn,ModifiedById
)
select NEWID(),temp.DeptRoleId,temp.UserId,GETDATE(),'00000000-0000-0000-0000-000000000001',GETDATE(),'00000000-0000-0000-0000-000000000001'  from #Tmp temp