已经有数据集结果如下:
FunctionID ModuleID FunctionCode FunctionName HasRole
01aa565b-8ef4-4ad3-9d06-3357a0a5abbf 8 X1-M8-01 浏览 0
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现在我要去掉第一行,因为这是同一个权限数据。
列HasRole,0表示没有该功能权限,非0则表示有该功能权限。期望数据集结果:
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怎么这中间的变化,怎么通过SQL语句去实现呢?
我自己的思路是这样的:
select *
from Table
where FunctionCode <> (select FunctionCode from Table where FunctionCode有重复)
and HasRole <> '0'可是怎么在条件里判读FunctionCode有重复呢。。
~~
FunctionID ModuleID FunctionCode FunctionName HasRole
01aa565b-8ef4-4ad3-9d06-3357a0a5abbf 8 X1-M8-01 浏览 0
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现在我要去掉第一行,因为这是同一个权限数据。
列HasRole,0表示没有该功能权限,非0则表示有该功能权限。期望数据集结果:
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怎么这中间的变化,怎么通过SQL语句去实现呢?
我自己的思路是这样的:
select *
from Table
where FunctionCode <> (select FunctionCode from Table where FunctionCode有重复)
and HasRole <> '0'可是怎么在条件里判读FunctionCode有重复呢。。
~~
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)
1,functionCode 不等于 已经重复过的functioncode
2,HasRole 不等于 '0'
光满足2,不满足1,无效的哇。不会影响下面的3条。这样有没有错?
(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
4楼的朋友,Max能这样用?这个会有可能存在的副作用么
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 #tableFunctionID 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 行受影响)