权限求和??(有点难度) 哪位高手来指点

[txt_ly]

说明：
--FIdSYRole(角色ＩＤ）,FunctionCode（模块代码）,add（增加权限　１表示有，０表示没有权限）

问题；
　　我现在要求角色ＩＤ为６，７的权限并集

数据：
drop table [dbo].[SY_RoleOper]
GO

CREATE 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

哪位高手来指点

[txt_ly]

自己先来顶一下！！！！！

[昵称被占用了]

'clientRecord',0,1,0,1,1,0
为什么不是
'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]

[昵称被占用了]

因为对于bit运算来说

b1 or b2 =max(b1,b2)

呵呵

[txt_ly]

服务器: 消息 409，级别 16，状态 2，行 1
maximum aggregate 运算不能以 bit 数据类型作为参数。

[txt_ly]

楼上的语句好像运行不了

[昵称被占用了]

晕哦,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]

[txt_ly]

哦，太棒了!!!!!!!~~~~
谢谢