读取权限的SQL设计，急，急，急！！！

Question

我对系统权限管理有以下几个表:1、(角色表)tblRolesrole_id    role_name   pid
1       角色一     0
11      角色二     1
111     角色三     11
2       角色四     0
...... 2、(角色用户分配表)tblAssign
role_id       user_Id
111            test
2              test
......3、（权限分配表）
role_id          add          delete              update
11                0             1                   0
111               1             0                   0
2                 0             0                   1
......这里有两个问题，请高手指教:
1、角色有层级关系，如果用户隶属一个子角色，那他便继承父角色的权限，如何通过SQL语读出某一用户隶属的所有角色？如：test用户隶属的角色便有（1,11,111,2）
2、因为test用户隶属于1,11,111,2角色，那它的权限便是这几个角色加起来的总和，能否通过SQL语句直接返回“或运算”之后的结果呢？如test用户得到的权限便有：
add        delete            update
1            1                 1

Answer 1

--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GOCREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.PID,@Level
FROM tb a,@t_Level b
WHERE a.ID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--上面的用户定义函数可以处理一个节点有多个父节点的情况，对于标准的树形数据而言，由于每个节点仅有一个父节点，所以也可以通过下面的用户定义函数实现查找标准树形数据的父节点。
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
END
RETURN
END

Answer 2

我是楼主,快来接分呀!
呵呵,第一个问题我已经通过循环的方式解决了,现在第二个,不知能不能实现!add          delete           update
0             1                 0
1             0                 0
1             1                 0否能把若干行的值通过"或运算"组合起来呀?
如把上面的示例,返回:
add          delete           update
1             1                 0

Answer 3

由于你权限是使用0，1表示，所以获取数据就比较简单了SELECT MAX(C.Add) AS Add, MAX(C.Delete) AS Delete, MAX(C.Update) AS Update
FROM 角色表 A, 角色用户分配表 B, 权限分配表 C
WHERE A.role_id = C.role_id
  AND A.role_id = B.role_id
  AND B.user_id = 'test'

Answer 4

直接使用 MAX 就可以了，因为1比0大啊，呵呵~~~

Answer 5

对于第一个问题，我不知道楼主使用的是什么数据库
如果是 oracle 就可以直接使用 connect by ... start with 之际得到所隶属的角色树

Answer 6

SELECT SYS_CONNECT_BY_PATH(A.pid, ',') || ',' || A.role_id AS roleid
FROM 角色表 A, 角色用户分配表 B
WHERE A.role_id = B.role_id
  AND B.user_id = 'test'
START WITH ...
CONNECT BY ...

Answer 7

TO:NekChan(骑蜗牛看星星) 
我用的是 Ms Sql,第1个问题实现的方式,之前想用游标+循环来实际递归,后面没试出来,就只接用循环搞定了.
第2个问题,由于存的是布尔类型,max无效.但我先转成tinyint再来max,也实现了.虽然问题自己解决了,
但还是非常感谢大家!