求一条sql语句

Question

我有一张表存的是用户id，权限id和部门id
另一张表使用树结构存储部门
使用oracle数据库

我想通过一条生气了语句查出用户所有权限，并且部门向上迭代出所有父部门，
这条sql语句该怎么写呢？

最终就是要拿到一个列表，用户id，权限id和部门id（所有父部门id）

Answer 1

我理解你的问题是每一个用户ID在一个部门中所有的权限，你给的数据好像不详细，我添加了一些数据，你看看满足需求不。
[code="sql"]
SQL> WITH authority AS (
2 SELECT '3029' kuid,'205' krid,'21' kdid FROM DUAL UNION ALL
3 SELECT '3029' kuid,'206' krid,'21' kdid FROM DUAL UNION ALL
4 SELECT '3029' kuid,'209' krid,'21' kdid FROM DUAL UNION ALL
5 SELECT '3030' kuid,'205' krid,'10080' kdid FROM DUAL UNION ALL
6 SELECT '3030' kuid,'206' krid,'10080' kdid FROM DUAL UNION ALL
7 SELECT '3030' kuid,'209' krid,'10080' kdid FROM DUAL UNION ALL
8 SELECT '3029' kuid,'205' krid,'10080' kdid FROM DUAL UNION ALL
9 SELECT '3029' kuid,'206' krid,'10080' kdid FROM DUAL UNION ALL
10 SELECT '3029' kuid,'209' krid,'10080' kdid FROM DUAL
11 ),
12 dept AS (
13 SELECT '0' kdid,'-1' kdpid FROM DUAL UNION ALL
14 SELECT '10080' kdid,'0' kdpid FROM DUAL UNION ALL
15 SELECT '21' kdid,'10080' kdpid FROM DUAL UNION ALL
16 SELECT '1' kdid,'-1' kdpid FROM DUAL UNION ALL
17 SELECT '20080' kdid,'1' kdpid FROM DUAL UNION ALL
18 SELECT '31' kdid,'20080' kdpid FROM DUAL
19 )
20 SELECT DISTINCT t1.kuid,
21 t1.krid,
22 t2.kdid kdpid
23 FROM authority t1
24 INNER JOIN (SELECT t.*,
25 CONNECT_BY_ROOT(t.kdid) root_id
26 FROM dept t
27 START WITH t.kdid IS NOT NULL
28 CONNECT BY PRIOR t.kdpid = t.kdid) t2 ON t1.kdid = t2.root_id
29 ORDER BY kuid,
30 kdpid,
31 krid
32 ;

KUID KRID KDPID

---

3029 205 0
3029 206 0
3029 209 0
3029 205 10080
3029 206 10080
3029 209 10080
3029 205 21
3029 206 21
3029 209 21
3030 205 0
3030 206 0
3030 209 0
3030 205 10080
3030 206 10080
3030 209 10080

15 rows selected
[/code]