关于一个sql的问题，请大家帮帮忙。特别邀请wildwave

Question

大家好，我曾经问过一个关于sql文的问题。（http://topic.csdn.net/u/20090916/14/45f1b3fe-b22a- 40a8-9465-2eee010c8bbf.html）现在，我又遇到了麻烦，想不出好的方法，来解决。
表A：
yonghuid（用户id）
quanxianid（权限id）
（联合主键，多对多）
表B：
quanxianid（权限id）
kongjianid（控件id）
（联合主键，多对多）
这个控件id，是指一个节点，通过这个节点，能从表C里找到它，及它的所有子节点
表C：
kongjianid（控件id，主键）
kongjianname（控件名称）
kongjian_p_id（父控件id）
（此表为一颗树形结构）
数据库是oracle10g我现在的需求是，我传递一个用户id进来，sql的查询结果是：
像上次问得一样，
把这个用户，对应的n个权限所各自对应的控件id及其子节点都找出来，然后，每个权限都有的控件id所对应的空间名字都输出。其他的不输出。
也就是说1个权限则，把其对应的“控件ID”和它的所有子控件的控件名称都输出。
N个权限则，把每个“权限ID”对应的“控件ID”及它的所有子控件都找到，然后输出的是所有权限都有（交集）的“控件名称”输出，其它不输出。 

Answer 1

你的那个贴，已经没有了如ls所说，把表结构和表的sample数据提供出来， 便于大家帮你解决

Answer 2

http://topic.csdn.net/u/20090916/14/45f1b3fe-b22a-40a8-9465-2eee010c8bbf.html连接可以。就是，中间多了个空格，我去掉了。这下可以了

Answer 3

试下
select distinct yonghuid,kongjianid from(
select a.yonghuid,c.kongjianid,
  count(distinct a.quanxianid)over(partition by a.yonghuid)c1,
  count(distinct a.quanxianid)over(partition by a.yonghuid,c.kongjianid)c2
from a,b,c
where a.quanxianid=b.quanxianid
start with b.kongjianid=c.kongjianid
connect by 
prior c.kongjianid=c.kongjian_p_id
)
where c1=c2
order by yonghuid

Answer 4

表A
YONGHU_ID       QUANXIAN_ID
a               0001
a               0002
b               0001
c               0003表B
QUANXIAN_ID CONTROL_ID
0001     10
0001     11
0001     9
0001     12
0001     15
0001     16
0001     17
0001     3
0001     4
0001     5
0001     6
0001     7
0002     8
0002     9
0002     11
0002     13
0002     14
0002     3
0002     4
0002     5
0002     6
0002     7
0003     19
0003     20
0003     2
0003     4
0003     5
0003     6
0003     7表C
CONTROL_ID CONTROL_NAME CONTROL_P_ID
1        MENU
2        btn_zhubiao 1
3        btn_xiaoshou 1
4        btn_gongying 1
5        btn_yuancailiao 1
6        btn_chengpin 1
7        btn_BiaoQian 1
8        btn11       2
9        btn12       2
10        btn13       2
11        btn21       2
12       btn22       2
13       btn23       2
14       btn31       2
15       btn32       2
16       btn33       2
17       btn41       2
18       btn11       3
19       btn12       3
20       btn13       3
21       btn11       4
22       btn12       4
23       btn13       4
24       btn11       5
25       btn12       5
26       btn13       5
27       btn21       5
28       btn22       5
29       btn23       5
30       btn31       5
31       btn32       5
32       btn11       6
33       btn12       6
34       btn13       6
35       btn21       6
36       btn22       6
37       btn23       6
38       btn31       6
39       btn32       6
40       btn11       7
41       btn12       7输入的条件是用户ID，
得到的是，此用户ID对应所有权限都有的控件id。希望大家帮帮忙。
之前，我大致也是这种方向。但是，需要优化。我刚才，跑了一下，数据库死掉了。上面的数据只是一部分，
所以，C表做递归，再连接，然后再3表连接，数据库受不了了。
我的用户ID只会输入一个  A.YONGHU_ID = 'COND1' 我觉得 这个条件必须先过滤，您再想想别的地方的优化。谢谢大家。

Answer 5

这样
select distinct yonghuid,k1 from(
select a.yonghuid,a.quanxianid,b.kongjianid,cc.kongjianid k1,
  count(distinct a.quanxianid)over(partition by a.yonghuid)c1,
  count(distinct a.quanxianid)over(partition by a.yonghuid,cc.kongjianid)c2
from a,b,
  (select kongjianid,kongjian_P_id,sys_connect_by_path(kongjianid,',')||',' c from c
  connect by prior kongjianid=kongjian_p_id
  start with kongjian_p_id is null)cc --这里我默认根节点的kongjian_p_id为空，如果不是，条件在这里修改
where a.quanxianid=b.quanxianid
  and instr(cc.c,','||b.kongjianid||',')>0
--and...条件可以加在这里)
where c1=c2
order by yonghuid;

Answer 6

create table a(yonghuid number,quanxianid number);
create table b(quanxianid number,kongjianid number);
create table c(kongjianid number,kongjianname varchar2(10),kongjian_p_id number);prompt Loading A...
insert into A (YONGHUID, QUANXIANID)
values (1, 1);
insert into A (YONGHUID, QUANXIANID)
values (1, 2);
insert into A (YONGHUID, QUANXIANID)
values (2, 1);
insert into B (QUANXIANID, KONGJIANID)
values (1, 11);
insert into B (QUANXIANID, KONGJIANID)
values (1, 22);
insert into B (QUANXIANID, KONGJIANID)
values (2, 11);
insert into C (KONGJIANID, KONGJIANNAME, KONGJIAN_P_ID)
values (11, 'a', null);
insert into C (KONGJIANID, KONGJIANNAME, KONGJIAN_P_ID)
values (22, 'b', null);
insert into C (KONGJIANID, KONGJIANNAME, KONGJIAN_P_ID)
values (33, 'c', 11);
insert into C (KONGJIANID, KONGJIANNAME, KONGJIAN_P_ID)
values (44, 'd', 11);
insert into C (KONGJIANID, KONGJIANNAME, KONGJIAN_P_ID)
values (55, 'e', 22);
给你我的测试数据

Answer 7

谢谢 wildwave兄弟了。我试试看。

Answer 8

你这个数据，是不是应该没有符合要求的记录选择出来呀11 -> 33
   -> 4422 -> 55这里两个权限的控件没有交集，应该没有数据选择出来吧。

Answer 9

也不知道我理解的对不对。我修改了一下wildwave兄弟的数据A表 用户有两个权限
  YONGHUID QUANXIANID
---------- ----------
         1          1
         1          2
         2          1B表 权限 对应 3个控件  11，22，44
QUANXIANID KONGJIANID
---------- ----------
         1         11
         1         22
         1         44
         2         11C表 控件父子关系
KONGJIANID KONGJIANNAME KONGJIAN_P_ID
---------- ------------ -------------
        11 a            
        33 c                       11
        44 d                       11
        22 b                       44
        55 e                       22父子关系如下
11 -> 33
   -> 44 -> 22 - 55
现在根据需求
权限用户1，对应的权限1，2已经对应控件为 11， 22， 44
在上面的控件树上可以发现1，2都拥有的控件为  22， 55结果为
22，55对应的控件的名字
试试下面sql，不知道是不是能满足你的功能和性能上的要求
select t_1.kongjianid, count(1), max(allCount)
  from (select distinct kongjianid
          from c
         start with kongjianid in
                    (select b.kongjianid
                       from a, b
                      where a.yonghuid = 1
                        and a.quanxianid = b.quanxianid)
        connect by prior kongjianid = kongjian_p_id) t_1,
       
       (select temp2.*, count(1) over() allCount
          from (select temp.root_kongjianid,
                       wmsys.wm_concat(fullpath) fullpath
                  from (select c.*,
                               connect_by_isleaf isleaf,
                               connect_by_root kongjianid as root_kongjianid,
                               sys_connect_by_path(kongjianid, ',') fullpath
                          from c
                         start with kongjianid in
                                    (select b.kongjianid
                                       from a, b
                                      where a.yonghuid = 1
                                        and a.quanxianid = b.quanxianid)
                        connect by prior kongjianid = kongjian_p_id) temp
                 where isleaf = 1
                 group by root_kongjianid) temp2) t_2
 where instr(',' || t_2.fullpath || ',', ',' || t_1.kongjianid || ',') > 0
 group by t_1.kongjianid
having count(1) = max(allCount)

Answer 10

这里lz的这句话N个权限则，把每个“权限ID”对应的“控件ID”及它的所有子控件都找到，然后输出的是所有权限都有（交集）的“控件名称”输出，其它不输出不知道是怎样的理解？这里的
三个权限1，是合并还是分开看

Answer 11

等楼主来解惑..
我是将每个权限所有的控件id及这个控件id的子控件在c表里全部找出，然后取交集加个控件名称字段
select distinct yonghuid,k1,kongjianname from(
select a.yonghuid,a.quanxianid,b.kongjianid,cc.kongjianid k1,cc.kongjianname,
  count(distinct a.quanxianid)over(partition by a.yonghuid)c1,
  count(distinct a.quanxianid)over(partition by a.yonghuid,cc.kongjianid)c2
from a,b,
  (select kongjianid,kongjianname,sys_connect_by_path(kongjianid,',')||',' c from c
  connect by prior kongjianid=kongjian_p_id
  start with kongjian_p_id is null)cc --这里我默认根节点的kongjian_p_id为空，如果不是，条件在这里修改
where a.quanxianid=b.quanxianid
  and instr(cc.c,','||b.kongjianid||',')>0
--and...条件可以加在这里)
where c1=c2
order by yonghuid;

Answer 12

你试试用户id是2的那条记录把
权限为2的那条的对应的控件改成22把22的父亲指定为44，看看是怎样的结果，这里的运行你的sql结果有些怪异。

Answer 13

改了，结果是这样
--a
YONGHUID QUANXIANID
1 1
1 2
2 1
--b
QUANXIANID KONGJIANID
1 11
1 22
2 22
--c
KONGJIANID KONGJIANNAME KONGJIAN_P_ID
11 a
22 b 44
33 c 11
44 d 11
55 e 22
YONGHUID K1 KONGJIANNAME
1 22 b
1 55 e
2 11 a
2 22 b
2 33 c
2 44 d
2 55 e
用户1有权限1和2
用户2有权限1
权限1，拥有控件11，（33，44，22，55），22（55）
权限2，拥有控件22，（55）
因此用户1拥有2个权限的交集：22，55
用户2有权限1的所有控件
没错啊

Answer 14

非常感谢，您的思路和写法都非常棒。
用了这个思路，我的目的达到了。非常感谢。
您对sql的感觉很宽，拓展了我的眼界。

Answer 15

wildwave以后，还要向你学习，请不吝赐教。谢谢。

Answer 16

select n.YONGHU_ID,n.QUANXIAN_ID,n.CONTROL_ID,C.CONTROL_ID,C.CONTROL_NAME,C.CONTROL_P_ID
  from (
select a.YONGHU_ID,a.QUANXIAN_ID,b.CONTROL_ID
  from a,b
 where a.QUANXIAN_ID=b.QUANXIAN_ID and a.YONGHU_ID='COND1') n ,C
start with n.CONTROL_ID=C.CONTROL_P_ID
connect by prior C.CONTROL_ID=C.CONTROL_P_ID 
and prior n.YONGHU_ID=n.YONGHU_ID and prior n.QUANXIAN_ID=n.QUANXIAN_ID and prior n.CONTROL_ID=n.CONTROL_ID
;
将递归出来的所有字段和记录摆在这里，你想怎么选就怎么选，这个语句应该属于最优化。

Answer 17

这样会出现笛卡尔积，有很多多余的记录
我上午也这么写过，测试错误，重写
刚刚根据这个思路重新写了一个
楼主可以比较一下效率
select distinct yonghuid,kongjianid,kongjianname from(
  select t.*,count(distinct quanxianid)over(partition by yonghuid)c1,
    count(distinct quanxianid)over(partition by yonghuid,kongjianid)c2 from(
      select replace(sys_connect_by_path(a.yonghuid,','),',','')yonghuid,
        replace(sys_connect_by_path(a.quanxianid,','),',','')quanxianid,
        c.kongjianid,c.kongjianname 
      from a inner join b on a.quanxianid=b.quanxianid
        --and a,b表的条件加这
      right join c on b.kongjianid=c.kongjianid
       --where ...c表条件加这里
      start with b.kongjianid=c.kongjianid
      connect by prior c.kongjianid=c.kongjian_p_id)t)
where c1=c2
order by yonghuid

Answer 18

那个replace不对，再改
select distinct yonghuid,kongjianid,kongjianname from(
  select t.*,count(distinct quanxianid)over(partition by yonghuid)c1,
    count(distinct quanxianid)over(partition by yonghuid,kongjianid)c2 from(
      select substr(sys_connect_by_path(a.yonghuid,','),2,instr(sys_connect_by_path(a.yonghuid,',')||',',',',1,2)-2)yonghuid,
        substr(sys_connect_by_path(a.quanxianid,','),2,instr(sys_connect_by_path(a.quanxianid,',')||',',',',1,2)-2)quanxianid,
        c.kongjianid,c.kongjianname 
      from a inner join b on a.quanxianid=b.quanxianid
        --and a,b表的条件加这
      right join c on b.kongjianid=c.kongjianid
       --where ...c表条件加这里
      start with b.kongjianid=c.kongjianid
      connect by prior c.kongjianid=c.kongjian_p_id)t)
where c1=c2
order by yonghuid