如下记录:
RoleId PermissionId Permission
1 1 101010
1 2 110000
2 1 100101
2 2 101010我要把相同PermissionId 的记录合并,Permission 栏位进行或运算
要求结果:
PermissionId Permission
1 101010 或 100101 = 101111
2 110000 或 101010 = 111010请交大虾
RoleId PermissionId Permission
1 1 101010
1 2 110000
2 1 100101
2 2 101010我要把相同PermissionId 的记录合并,Permission 栏位进行或运算
要求结果:
PermissionId Permission
1 101010 或 100101 = 101111
2 110000 或 101010 = 111010请交大虾
select PermissionId,replace(convert(varchar(6),sum(Permission)),'2','1')
from tablename
group by PermissionId
PermissionId,
Replace(Right(1000000 + SUM(Cast(Permission As Int)), 6),'2','1')
From TEST
Group By
PermissionId
(RoleId Int,
PermissionId Int,
Permission Char(6))
Insert TEST Select 1, 1, '101010'
Union All Select 1, 2, '110000'
Union All Select 2, 1, '100101'
Union All Select 2, 2, '101010'
Union All Select 3, 3, '000101'
Union All Select 3, 3, '001010'
GO
--
Select
PermissionId,
Replace(Right(1000000 + SUM(Cast(Permission As Int)), 6),'2','1') As Permission
From TEST
Group By
PermissionId
GO
Drop Table TEST
--Result
/*
PermissionId Permission
1 101111
2 111010
3 001111
*/
不知道
Replace(Right(1000000 + SUM(Cast(Permission As Int)), 6),'2','1') As Permission
这句什么意思
我上面举例只是有两个
(RoleId Int,
PermissionId Int,
Permission Char(6))
Insert TEST Select 1, 1, '101010'
Union All Select 1, 2, '110000'
Union All Select 1, 1, '101110'
Union All Select 2, 1, '100101'
Union All Select 2, 2, '101010'
Union All Select 3, 3, '000101'
Union All Select 3, 3, '001010'
GO
--
Select
PermissionId,(case when SUM(cast(substring(Permission,1,1) as int))>0 then '1' else '0' end) +
(case when SUM(cast(substring(Permission,2,1) as int))>0 then '1' else '0' end) +
(case when SUM(cast(substring(Permission,3,1) as int))>0 then '1' else '0' end) +
(case when SUM(cast(substring(Permission,4,1) as int))>0 then '1' else '0' end) +
(case when SUM(cast(substring(Permission,5,1) as int))>0 then '1' else '0' end) +
(case when SUM(cast(substring(Permission,6,1) as int))>0 then '1' else '0' end) as Permission
From TEST
Group By
PermissionId
GO
Drop Table TEST