34,590
社区成员
发帖
与我相关
我的任务
分享
--测试表(部门,权限)
create table test(DepartmentID int, Permission int)
insert into test values(1,3)
insert into test values(1,14)
insert into test values(2,2)
insert into test values(2,4)
insert into test values(3,1)
insert into test values(3,15)
--权限统计结果
DepartmentID Permission
------------ -----------
1 15
2 6
3 15
多权限这样用
create table test(DepartmentID int, Permission int)
insert into test values(1,3)
insert into test values(1,14)
insert into test values(1,16)
insert into test values(2,2)
insert into test values(2,4)
insert into test values(3,1)
insert into test values(3,15)
go
;with a
as
(
select DepartmentID,Permission,Row=ROW_NUMBER()over(partition by DepartmentID order by DepartmentID)
from test
),a2
as
(
select *,Permission2=Permission from a where Row=1
union all
select a.*,b.Permission2|a.Permission from a inner join a2 as b on a.DepartmentID=b.DepartmentID and a.Row=b.Row+1
)
select a.DepartmentID,a.Permission2 as Permission from a2 as a where not exists(select 1 from a2 where DepartmentID=a.DepartmentID and Row>a.Row)
order by 1
/*
DepartmentID Permission
1 31 --(3|14|16)
2 6
3 15
*/
--测试表(部门,权限)
create table test(DepartmentID int, Permission int)
insert into test values(1,3)
insert into test values(1,14)
insert into test values(2,2)
insert into test values(2,4)
insert into test values(3,1)
insert into test values(3,15)
--如果都只有两个权限的话:
select a.departmentid,a.permission|b.permission
from test a inner join test b on a.DepartmentID=b.DepartmentID and a.Permission <b.Permission
--如果有多个权限的话,要用递归
;with c1 as(
select ROW_NUMBER()over(PARTITION by departmentid order by Permission)rn,* from test
),c2 as(
select rn,departmentid,permission from c1 where rn=1
union all
select b.rn,a.departmentid,a.Permission|b.Permission from c2 a inner join c1 b on a.DepartmentID=b.DepartmentID and a.rn=b.rn-1
)select DepartmentID,MAX(permission) as permission from c2 group by DepartmentID
/*
DepartmentID permission
------------ -----------
1 15
2 6
3 15
(3 行受影响)
*/
go
drop table test
;with a
as
(
select DepartmentID,Permission,Row=ROW_NUMBER()over(partition by DepartmentID order by DepartmentID)
from test
)
select
a.DepartmentID,
Permission=a.Permission|b.Permission
from a
inner join a as b on a.DepartmentID=b.DepartmentID and a.row=b.row-1
/*
DepartmentID Permission
1 15
2 6
3 15*/
select DepartmentID,sum(Permission) as Permission from test group by DepartmentID