请教 ORACLE 一个稍微复杂的SQL文的拼写！

Question

首先，感谢各位的帮忙。请教一个sql。
或者如果您有好的建议，可以采用其它方式，例如存储过程，或代码解决。内容如下：
表A：
№ Ｐ 字段名称
1 ○ 用户ID
2 ○ 权限ID表B：
№ Ｐ 字段名称
1 ○ 权限ID
2 ○ 画面ID
3 ○ 控件ID需求：一个用户可以有1-N个权限。现在，参数是“用户ID”和“画面ID”，要得到“控件ID”。
其中一个用户有
1个权限则，把所有“控件ID”都输出。
N个权限则，把这所有N个“权限ID”，画面ID，都有（交集）的“控件ID”输出，其它不输出。举例：
表A：用户ID   权限ID
     user1      a
     user2      a
     user2      b
表B：权限ID   画面ID   控件ID
       a      page1    control1
       a      page1    control2
       a      page2    control1
       b      page1    control1
       b      page1    control3
我的参数是：用户ID=“user1”，画面ID=“page1”则，输出是：control1
                                                          control2
我的参数是：用户ID=“user2”，画面ID=“page1”则，输出是：control1希望大家都理解了。更希望大家帮帮忙。谢谢。

Answer 1

create table a(userid varchar2(10),privid varchar2(2));
create table b(privid varchar2(2),picid varchar2(10),cid varchar2(10));
insert into a values('user1','a');
insert into a values('user2','a');
insert into a values('user2','b');
insert into b values('a','page1','cont1');
insert into b values('a','page1','cont2');
insert into b values('a','page2','cont1');
insert into b values('b','page1','cont1');
insert into b values('b','page1','cont3');   create or replace procedure proc(v_user varchar2, v_pic varchar2) is 
      v_flag number := 0;
   begin 
      select count(privid) into v_flag from a where userid = v_user;
      
      if v_flag = 0 then
         dbms_output.put_line('NO RECORD FOUND');
      elsif v_flag = 1 then
         for i in (select cid from b, a where b.privid = a.privid and a.userid = v_user and b.picid = v_pic) loop
            dbms_output.put_line(i.cid);
         end loop;
      else 
         for i in (select cid, count(1) from(select cid from b, a 
                                             where b.privid = a.privid and a.userid = v_user and b.picid = v_pic
                                            )
                   having count(1) > 1 group by cid) loop
            dbms_output.put_line(i.cid);
         end loop;
      end if; 
   end proc;
/SQL> execute proc('user1','page1');cont1
cont2PL/SQL procedure successfully completedSQL> execute proc('user2','page1');cont1PL/SQL procedure successfully completedSQL> 

Answer 2

非常感谢您的回答，让我受益匪浅。
感觉自己要是能这么快写出这么漂亮的pl/sql就好了。我很少写PL/SQL，我想请问下，是不是，我把
having count(1) > 1 group by cid) loop
改成 having count(1) = v_flag group by cid) loop
就可以满足我的需求了。
我需要，n个权限的话，每个权限都要（交集）的“控件ID”输出，其它不输出。
count(1) > 1。好像只要两个权限以上有就可以了。

Answer 3

select distinct 用户id,控件id from(
select 用户id,权限id,画面id,控件id,c,
  count(distinct 权限id)over(partition by 控件id)c1 from(
select a.用户id,a.权限id,b.画面id,b.控件id,
  count(distinct a.权限id)over(partition by a.用户id)c
from a,b
where a.权限id=b.权限id
  and a.用户id=:userid
  and b.画面id=:imgid))
where c=1 or c1=c

Answer 4

select distinct 用户id,控件id from(
select a.用户id,b.控件id,
  count(distinct a.权限id)over(partition by a.用户id)c,
  count(distinct a.权限id)over(partition by b.控件id)c1
from a,b
where a.权限id=b.权限id
  and a.用户id=:userid 
  and b.画面id=:imgid)
where c=1 or c1=c
简化下
:userid 和:imgid替换成你要的值

Answer 5

wildwave，谢谢。
哇塞，要是能用sql文，这样写。是最好的。
但是，我有不明白的地方。请教一下。请指点。
count(distinct a.权限id)over(partition by a.用户id)c,
这句话，能不能给解释一下，让我也学学，我知道partition 用来分区，但是效果，不清楚。
另外，第一句
select distinct 用户id,控件id from( 我觉得，可能有点问题。我只要控件id，为什么要加上distinct 用户id。用户id，只有一个。
而输出的控件id，可能有很多个。再次感谢您的帮忙。

Answer 6

这个代码的意思就是：
c为符合a.用户id=:userid and b.画面id=:imgid要求的用户对应的权限数目
c1为每个控件id所对应的权限数目
当控件对应的权限数与当前用户对应的所有权限数相同的时候
说明用户的每个权限都对应了该控件id
将这这样的id输出
distinct是为了将重复记录去掉。用户id是只有一个，当控件id可能重复
你测试下就知道了
count(distinct a.权限id)over(partition by b.控件id)
这句改成
count(distinct a.权限id)over(partition by a.用户id,b.控件id)
更严谨
这样的话用户数不为1也没关系
select distinct 用户id,控件id from( 
select a.用户id,b.控件id, 
  count(distinct a.权限id)over(partition by a.用户id)c, 
  count(distinct a.权限id)over(partition by a.用户id,b.控件id)c1 
from a,b 
where a.权限id=b.权限id 
  and a.用户id=:userid 
  and b.画面id=:imgid) 
where c1=c 

Answer 7

是的，改成v_flag就可以了

Answer 8

非常感谢两位。
两位都是很牛的。
特别是sql文，学习到不少，本来觉得这么复杂，一个sql出不来。看来还是火候未到。
总之，感谢两位。