sql - Oracle 分层查询

Question

使用 Oracle 10g。我有两张 table :

User   Parent
-------------
1      (null)
2      1
3      1
4      3

Permission  User_ID
-------------------
A           1
B           3

权限表中的值会继承给子级。我想编写一个可以返回如下内容的查询:

User    Permission
------------------
 1       A
 2       A
 3       A
 3       A
 3       B
 4       A
 4       B

是否可以使用 10g connect .. by 语法来制定这样的查询，以从先前级别提取行？

Answer 1

您可以使用连接方式(以及返回根节点的列值的函数CONNECT_BY_ROOT)来实现所需的结果:

SQL> WITH users AS (
  2     SELECT 1 user_id, (null) PARENT FROM dual
  3     UNION ALL SELECT 2, 1 FROM dual
  4     UNION ALL SELECT 3, 1 FROM dual
  5     UNION ALL SELECT 4, 3 FROM dual
  6  ), permissions AS (
  7     SELECT 'A' permission, 1 user_id FROM dual
  8     UNION ALL SELECT 'B', 3 FROM dual
  9  )
 10  SELECT lpad('*', 2 * (LEVEL-1), '*')||u.user_id u,
 11         u.user_id, connect_by_root(permission) permission
 12    FROM users u
 13    LEFT JOIN permissions p ON u.user_id = p.user_id
 14  CONNECT BY u.PARENT = PRIOR u.user_id
 15   START WITH p.permission IS NOT NULL
 16  ORDER SIBLINGS BY user_id;

U         USER_ID PERMISSION
--------- ------- ----------
3               3 B
**4             4 B
1               1 A
**2             2 A
**3             3 A
****4           4 A