我有一张表存的是用户id,权限id和部门id
另一张表使用树结构存储部门
使用oracle数据库
我想通过一条生气了语句查出用户所有权限,并且部门向上迭代出所有父部门,
这条sql语句该怎么写呢?
最终就是要拿到一个列表,用户id,权限id和部门id(所有父部门id)
我理解你的问题是每一个用户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]
t1: user_id,quanxian_id,dept_id
t2: dept_id,dept_name,parent_dept_id
查询用户ID的所有父部门的SQL如下:
[code="SQL"]select t1.user_id,t1.quanxian_id,t1.dept_id
from t1,t2
where t1.dept_id = t2.dept_id
START WITH t1.user_id = 某用户的ID
CONNECT BY PRIOR t1.parent_dept_id = t1.dept_id[/code]
或者查询所有用户的:
[code="SQL"]select t1.user_id,t1.quanxian_id,t1.dept_id
from t1,t2
where t1.dept_id = t2.dept_id
START WITH t1.user_id is not null
CONNECT BY PRIOR t1.parent_dept_id = t1.dept_id[/code]
供参考~
t1: user_id,quanxian_id,dept_id
t2: dept_id,dept_name,parent_dept_id
select user_id,quanxian_id,parent_dept_id
from t1,t2
where t1.dept_id = t2.dept_id
t1(user_id,qx_id,dept_id)
t2( dept_id,dept_name,parent_dept_id )
select t1.user_id,t1.qx_id,t2.dept_id
from t1,t2
where t1.dept_id = t2.dept_id
START WITH t1.user_id=用户ID
CONNECT BY PRIOR t1.dept_id
= t2.parent_dept_id
[code="sql"]
t1(user_id,qx_id,dept_id)
t2( dept_id,dept_name,parent_dept_id )
select t1.user_id,t1.qx_id,t2.dept_id
from t1,t2
where t1.dept_id = t2.dept_id
START WITH t1.user_id=用户ID
CONNECT BY PRIOR t1.dept_id
= t2.parent_dept_id
[/code]
你把表的的数据贴上,和想要的结果
[quote]这样不能查到我要的结果, user_id,quanxian_id相同的时候,dept_id的父节点的dept_id不在t1这张表中,我要的到一个结果集,就是 user_id,quanxian_id确定然后根据dept_id去查找父节点,再配上user_id,quanxian_id获得一个列表 [/quote]
不在一个表上没关系,可加到t2后面,关联查询就可以了,可适当扩展下
[code="xml"]
select t1.user_id,t1.quanxian_id,t1.dept_id
from t1,t2,t3
where t1.dept_id = t2.dept_id
and t3.
...
[/code]
你应该把测试脚本都写上,搞个表别人还得写脚本