mysql组织树遍历_关于mysql：复杂的SQL查询：选择像树遍历

考虑以下(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或查看本文。