從其他表中查找具有組值的行

...

...

如上所示，我有 2 個表 Table1 和 Table2。我的要求是從具有組中所有角色的 table2 中獲取 User_ID。此外，僅需要檢查至少具有 2 個角色的組。如果 group_ID 只有 1 個角色，則不應考慮

例如，這就是上面 2 個表格的結果

user1 具有組 1 (A,B) 中的兩個角色 -> 因此它在結果中。

user3 具有第 2 組 (A,D) 中的兩個角色 -> 因此它在結果中。

user5 具有組 1(A,B)、2(A,D) 和 3(A,B,C) 中的所有角色 -> 因此它在結果中。

User2 的角色 C 和 D 不是一個組，因此未顯示在結果中

User4 的角色 C 是一個組 (Group_ID = 4)，但該組應該至少有 2 個角色，因此未顯示在結果中

....

Select Table2.USER_ID,Table1.GROUP_ID from Table2, Table1 Where Table2.ROLE = Table1.ROLE group by Table1.GROUP_ID,Table2.USER_ID

With the above query, I am able to get the records with user_id assigned any of the role. However, I want to get the User_ID from the table2 which has all the roles from a group.

Any help is much appreciated. I will also make sure to accept the answer leading me to the solution

uj5u.com熱心網友回復：

這是一個適用于 Oracle 資料庫的解決方案；為 SQL Server 調整它（如果可能的話；我不知道那種方言）。

測驗資料（其他人也可以使用）：

create table table1(id number, role varchar2(10), group_id number);

insert into table1 (id, role, group_id)
    select 1, 'A', 1 from dual union all
    select 2, 'B', 1 from dual union all
    select 3, 'A', 2 from dual union all
    select 4, 'D', 2 from dual union all
    select 5, 'A', 3 from dual union all
    select 6, 'B', 3 from dual union all
    select 7, 'C', 3 from dual
;

create table table2 (user_id varchar2(20), role varchar2(10));

insert into table2 (user_id, role)
    select 'user1', 'A' from dual union all
    select 'user1', 'B' from dual union all
    select 'user2', 'C' from dual union all
    select 'user2', 'D' from dual union all
    select 'user3', 'A' from dual union all
    select 'user3', 'D' from dual union all
    select 'user4', 'C' from dual union all
    select 'user5', 'A' from dual union all
    select 'user5', 'B' from dual union all
    select 'user5', 'C' from dual union all
    select 'user5', 'D' from dual
;

commit;

創建用戶定義的資料型別（代表角色的字串集合）：

create or replace type role_list as table of varchar2(10);
/

查詢和輸出：

select a2.user_id, a1.group_id
from   (
         select user_id, cast(collect(role) as role_list) as user_roles
         from   table2
         group  by user_id
       ) a2
       inner join
       (
         select group_id, cast(collect(role) as role_list) as group_roles
         from   table1
         group  by group_id
       ) a1
       on a1.group_roles submultiset of a2.user_roles
order  by user_id, group_id
;

USER_ID         GROUP_ID
------------  ----------
user1                  1
user3                  2
user5                  1
user5                  2
user5                  3

該策略非常明顯，應該很容易直接從代碼中讀取。group_id在第一個表中按角色分組，user_id在第二個表中按角色分組。識別在用戶的角色串列中找到組的所有角色的所有對（用戶、組）——這正是submultiset比較運算子所做的。

A more rudimentary query (harder to follow and maintain, and likely slower), but perhaps helpful as it is likely to work with very few changes - if any - in pretty much all SQL dialects, might look like this. Assuming role can't be null in table2 (to make the query slightly simpler):

select q2.user_id, q1.group_id
from   (select distinct user_id  from table2) q2
       cross join
       (select distinct group_id from table1) q1
where  not exists 
       (
         select role
         from   table1
         where  group_id = q1.group_id
           and  role not in
                     (
                       select role
                       from   table2
                       where  user_id = q2.user_id
                     )
       )
order  by user_id, group_id
;

uj5u.com熱心網友回復：

將需求直接翻譯成 SQL（Oracle 風格）

with g as (
select group_id, count(*) role_cnt from table1 group by group_id having count(*) > 1),
u as (
select u.user_id, g.group_id, count(*) usergroup_role_cnt from table1 g, table2 u
where u.role=g.role
group by u.user_id, g.group_id)
select u.user_id, g.group_id from g, u
where u.group_id=g.group_id
and u.usergroup_role_cnt=g.role_cnt;

標籤：sql sql-server oracle nested-queries

上一篇：WHERE子句在CTE之后找不到列？

下一篇：如何重寫此查詢以在Postgres中使用LEFT/RIGHTINNER/OUTERJOIN關鍵字洗掉Oracle的( )？[復制]