说明:
--FIdSYRole(角色ID),FunctionCode(模块代码),add(增加权限 1表示有,0表示没有权限)问题;
我现在要求角色ID为6,7的权限并集数据:
drop table [dbo].[SY_RoleOper]
GOCREATE TABLE [dbo].[SY_RoleOper] (
[FId] [PKId] NOT NULL ,
[FIdSYRole] [FKID] NOT NULL ,
[FunctionCode] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[add] [bit] NULL ,
[edit] [bit] NULL ,
[del] [bit] NULL ,
[browse] [bit] NULL ,
[print] [bit] NULL ,
[check] [bit] NULL
) ON [PRIMARY]
GO
insert into SY_RoleOper values(350,6,'clientRecord',0,1,0,1,1,0 )
insert into SY_RoleOper values(351,7,'clientRecord',0,1,0,1,1,0 )
insert into SY_RoleOper values(352,8,'clientRecord',1,1,0,1,1,1 )
insert into SY_RoleOper values(353,6,'linkman',0,1,0,1,1,0)
insert into SY_RoleOper values(353,7,'linkman',1,1,0,1,1,1)结果
'clientRecord',0,1,0,1,1,0
'linkman',1,1,0,1,1,1哪位高手来指点
--FIdSYRole(角色ID),FunctionCode(模块代码),add(增加权限 1表示有,0表示没有权限)问题;
我现在要求角色ID为6,7的权限并集数据:
drop table [dbo].[SY_RoleOper]
GOCREATE TABLE [dbo].[SY_RoleOper] (
[FId] [PKId] NOT NULL ,
[FIdSYRole] [FKID] NOT NULL ,
[FunctionCode] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[add] [bit] NULL ,
[edit] [bit] NULL ,
[del] [bit] NULL ,
[browse] [bit] NULL ,
[print] [bit] NULL ,
[check] [bit] NULL
) ON [PRIMARY]
GO
insert into SY_RoleOper values(350,6,'clientRecord',0,1,0,1,1,0 )
insert into SY_RoleOper values(351,7,'clientRecord',0,1,0,1,1,0 )
insert into SY_RoleOper values(352,8,'clientRecord',1,1,0,1,1,1 )
insert into SY_RoleOper values(353,6,'linkman',0,1,0,1,1,0)
insert into SY_RoleOper values(353,7,'linkman',1,1,0,1,1,1)结果
'clientRecord',0,1,0,1,1,0
'linkman',1,1,0,1,1,1哪位高手来指点
为什么不是
'clientRecord',1,1,0,1,1,1应该or关系
select [FunctionCode],max([add]) as [add],max([edit])as [edit]
,max([del]) as [del],max([browse])as [browse]
,max([print]) as [print],max([check])as [check]
from ].[SY_RoleOper]
where [FIdSYRole] in (6,7)
group by [FunctionCode]
maximum aggregate 运算不能以 bit 数据类型作为参数。
select [FunctionCode],max(cast([add] as tinyint)) as [add],max(cast([edit] as tinyint))as [edit]
,max(cast([del] as tinyint)) as [del],max(cast([browse] as tinyint))as [browse]
,max(cast([print] as tinyint)) as [print],max(cast([check] as tinyint))as [check]
from ].[SY_RoleOper]
where [FIdSYRole] in (6,7)
group by [FunctionCode]