考虑以下(1:N)关系:
[entity: user] [entity: rid]。
将两个表中的数据视为:
select * from user;
user-id rid-key
a-basa a
b-basa b
a.a-basa a.a
a.b-basa a.b
a.a.a-basa a.a.a
a.a.b-basa a.a.b
a.b.a-basa a.b.a
a.b.b-basa a.b.b
a.b.b.a-basa a.b.b.a
a.b.b.b-basa a.b.b.b
select * from rid;
rid-key parent-rid enabled
a null true
b null true
a.a a true
a.b a false
a.a.a a.a true
a.b.a a.b true
a.b.b a.b true
a.b.b.a a.b.b true
......
n rows
我需要设计一个将输入user-id的单个查询(非存储过程),并考虑以下事实:
如果授予用户访问rid的权限,那么它也可以访问给定的rid的parent rid-rid本身已启用(enabled = true).
这应该持续到我们到达root rid为止。 parent rid属性是null。
在上面的示例中,用户'a.b.b.a-basa'的可访问rid的列表将为:
a.b.b.a
a.b.b
a.b
对于a.a.a-basa:
a.a.a
a.a
a
我们可以使用单个查询获取此列表吗? 任何SQL供应商都可以。
对于问题+1,使用循环可以使... :)容易得多,但是无论如何您都需要查询...
水平数是已知的吗? 如果没有,我认为这将是不可能的,因为SQL不支持递归。
您检查了stackoverflow.com/questions/2319284/吗?
@cha:SQL(语言)支持递归。 MySQL不执行SQL(仅通过过程和功能)。
通过这种特殊设计,甚至不需要递归查询。 可以使用一种可以在所有DBMS中使用的简单方法来完成。
谢谢大家的评论。 我学到新东西。en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
@ypercube:您应该添加显示该查询的答案。
分层数据有几种模型。大多数模型(如"邻接表")都需要对某些查询进行某种形式的递归。使用使用物化路径模型的设计,无需递归查询就可以实现所需的功能。
在SQL-fiddle test-mysql上的MySQL中进行测试(没有递归查询)。如果您修改字符串连接部分,则可以轻松地将其转换为其他DBMS:
SELECT
COUNT(*)-1 AS steps_up,
rid2.rid_key AS ancestor_rid_key
FROM
u2
JOIN
rid
ON u2.rid_key = rid.rid_key
OR u2.rid_key LIKE CONCAT(rid.rid_key, '.%')
JOIN
rid AS rid2
ON rid.rid_key = rid2.rid_key
OR rid.rid_key LIKE CONCAT(rid2.rid_key, '.%')
WHERE
u2.userid = 'basa'
AND
u2.rid_key = 'a.b.b.a'
GROUP BY
rid2.rid_key, rid2.enabled
HAVING
COUNT(*) + (rid2.enabled = 'true')
= SUM(rid.enabled = 'true') + 1 ;
它使用此视图,虽然不是必须的,但它表明user.user_id正在存储rid_key列中已经具有的数据。
还有一点需要注意的是,以上查询根本不使用parent_rid列。而且我相信它可以进一步改进。
Oracle解决方案:
SQL> select u.user_id, r.rid_key, r.parent_rid, r.enabled
2 from users u
3 inner join rid r
4 on r.rid_key = u.rid_key
5 start with u.user_id = 'a.a.a-basa'
6 connect by prior r.parent_rid = r.rid_key and prior enabled = 'true'
7 /
USER_ID RID_KEY PAREN ENABL
------------ ------- ----- -----
a.a.a-basa a.a.a a.a true
a.a-basa a.a a true
a-basa a null true
SQL> select u.user_id, r.rid_key, r.parent_rid, r.enabled
2 from users u
3 inner join rid r
4 on r.rid_key = u.rid_key
5 start with u.user_id = 'a.b.b.a-basa'
6 connect by prior r.parent_rid = r.rid_key and prior enabled = 'true'
7 /
USER_ID RID_KEY PAREN ENABL
------------ ------- ----- -----
a.b.b.a-basa a.b.b.a a.b.b true
a.b.b-basa a.b.b a.b true
a.b-basa a.b a false
http://sqlfiddle.com/#!4/d529f/1
这应该使球滚动起来。
答案适用于SQL Server 2005及更高版本
DECLARE @UsersRIDkey VARCHAR(10) = 'a.a.a'
;WITH UserCTE (userid, ridkey) AS
(
SELECT 'a-basa', 'a' UNION ALL
SELECT 'b-basa', 'b' UNION ALL
SELECT 'a.a-basa', 'a.a' UNION ALL
SELECT 'a.b-basa', 'a.b' UNION ALL
SELECT 'a.a.a-basa', 'a.a.a' UNION ALL
SELECT 'a.a.b-basa', 'a.a.b' UNION ALL
SELECT 'a.b.a-basa', 'a.b.a' UNION ALL
SELECT 'a.b.b-basa', 'a.b.b' UNION ALL
SELECT 'a.b.b.a-basa', 'a.b.b.a' UNION ALL
SELECT 'a.b.b.b-basa', 'a.b.b.b'
)
,RidCTE (ridkey, parentrid, isenabled) AS
(
SELECT 'a', null, 1 UNION ALL
SELECT 'b', null, 1 UNION ALL
SELECT 'a.a', 'a', 1 UNION ALL
SELECT 'a.b', 'a', 0 UNION ALL
SELECT 'a.a.a', 'a.a', 1 UNION ALL
SELECT 'a.b.a', 'a.b', 1 UNION ALL
SELECT 'a.b.b', 'a.b', 1 UNION ALL
SELECT 'a.b.b.a', 'a.b.b', 1
)
,RidHierarchyCTE AS
(
SELECT *
FROM RidCTE
WHERE ridkey = @UsersRIDkey
UNION ALL
SELECT R.ridkey, R.parentrid, R.isenabled
FROM RidHierarchyCTE H
JOIN RidCTE R ON R.ridkey = H.parentrid
)
SELECT ridkey
FROM RidHierarchyCTE
应该也适用于PostgreSQL和DB2(除了变量定义)。 顺便说一句:您应该习惯于将语句终止符(;)放在所属的位置:最后:sqlblog.com/blogs/aaron_bertrand/archive/2009/09/03/
您说得对...我仍在努力养成习惯^^
在Oracle中,您可以使用层次查询来实现。搜索CONNECT BY或查看本文。