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

 

1.sql 查询和删除多条字段的重复语句,布布扣,bubuko.com

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

上一篇:Oracle 中的replace函数的应用


下一篇:oracle整体知识的大致介绍(1)-概念