1.SQL 查詢和刪除多條欄位的重複語句

標籤：style   blog   color   div   new   sql   log   user   

查詢

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_UserDeptRoleinsert 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