34,588
社区成员
发帖
与我相关
我的任务
分享
create table #table
(FunctionID nvarchar(100),
ModuleID int,
FunctionCode nvarchar(50),
FunctionName nvarchar(10),
HasRole nvarchar(100))
insert #table
select '01aa565b-8ef4-4ad3-9d06-3357a0a5abbf', 8,'X1-M8-01','浏览','0' union all
select '01aa565b-8ef4-4ad3-9d06-3357a0a5abbf', 8,'X1-M8-01','浏览','01aa565b-8ef4-4ad3-9d06-3357a0a5abbf' union all
select '1b07359d-7199-4e68-b487-8dea02a52b5c', 8,'X1-M8-04','修改','0' union all
select '9e672f45-fda9-4bcc-b938-bae2faabfa1c', 8,'X1-M8-02','新增','0' union all
select 'df6d92d9-b6e5-4ef3-aba7-a78e3bdeae02', 8,'X1-M8-03','删除','0'
delete A
from #table A
where A.HasRole='0'
and exists(select 1 from #table B
where A.ModuleID=B.ModuleID
and A.FunctionCode=B.FunctionCode
and A.FunctionName=B.FunctionName
and B.HasRole<>'0')
select * from #table
FunctionID ModuleID FunctionCode FunctionName HasRole
---------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------- ------------ ----------------------------------------------------------------------------------------------------
01aa565b-8ef4-4ad3-9d06-3357a0a5abbf 8 X1-M8-01 浏览 01aa565b-8ef4-4ad3-9d06-3357a0a5abbf
1b07359d-7199-4e68-b487-8dea02a52b5c 8 X1-M8-04 修改 0
9e672f45-fda9-4bcc-b938-bae2faabfa1c 8 X1-M8-02 新增 0
df6d92d9-b6e5-4ef3-aba7-a78e3bdeae02 8 X1-M8-03 删除 0
(4 行受影响)
create table #table
(FunctionID nvarchar(100),
ModuleID int,
FunctionCode nvarchar(50),
FunctionName nvarchar(10),
HasRole nvarchar(100))
insert #table
select '01aa565b-8ef4-4ad3-9d06-3357a0a5abbf', 8,'X1-M8-01','浏览','0' union all
select '01aa565b-8ef4-4ad3-9d06-3357a0a5abbf', 8,'X1-M8-01','浏览','01aa565b-8ef4-4ad3-9d06-3357a0a5abbf' union all
select '1b07359d-7199-4e68-b487-8dea02a52b5c', 8,'X1-M8-04','修改','0' union all
select '9e672f45-fda9-4bcc-b938-bae2faabfa1c', 8,'X1-M8-02','新增','0' union all
select 'df6d92d9-b6e5-4ef3-aba7-a78e3bdeae02', 8,'X1-M8-03','删除','0'
select FunctionID,ModuleID,FunctionCode,FunctionName,max(HasRole) as HasRole from #table
group by FunctionID,ModuleID,FunctionCode,FunctionName
--FunctionID ModuleID FunctionCode FunctionName HasRole
--------------------------------------- ----------- -------------- -------------- ------------------------
--01aa565b-8ef4-4ad3-9d06-3357a0a5abbf 8 X1-M8-01 浏览 01aa565b-8ef4-4ad3-9d06-3357a0a5abbf
--1b07359d-7199-4e68-b487-8dea02a52b5c 8 X1-M8-04 修改 0
--9e672f45-fda9-4bcc-b938-bae2faabfa1c 8 X1-M8-02 新增 0
--df6d92d9-b6e5-4ef3-aba7-a78e3bdeae02 8 X1-M8-03 删除 0
delete A
from table_test A
where A.HasRole=0
and exists(select 1 from table_test B
where A.ModuleID=B.ModuleID
and A.FunctionCode=B.FunctionCode
and A.FunctionName=B.FunctionName
and B.HasRole<>0)