我有3個表，需要寫一個查詢：根據成員表中的request_id，我需要做以下事情：為每個成員找到他所屬的組并顯示group_type和所有用戶屬于該組。因此，對于成員 [email protected]，他屬于組 A，并且有 2 個用戶屬于該組（[email protected] 和 [email protected]）。我們需要同時顯示兩者。對于成員[email protected]，他屬于B組，沒有其他人屬于該組。我們只顯示 1 條記錄
--- users 表用于將成員與組表鏈接。用戶、成員、組
users tble
----------
user_id email
1 mike@yahoo.com
2 sam@yahoo.com
3 peter@yahoo.com
create table users_tbl (id number, email varchar2);
insert into users_tbl values(1, '[email protected]');
insert into users_tbl values(2, '[email protected]');
insert into users_tbl values(3, '[email protected]');
members tble
------------
member_id request_id email
1 123 mike@yahoo.com
2 123 peter@yahoo.com
create table members_tbl (member_id number, request_id number, email varchar2);
insert into members_tbl values(1, 123, '[email protected]');
insert into members_tbl values(2, 123, '[email protected]');
groups table
------------
group_id group_type user_id
1 A 1
2 A 2
3 B 3
create table groups_tbl (group_id number, group_type varchar2, user_id number);
insert into groups_tbl values (1, 'A', 1);
insert into groups_tbl values (2, 'A', 2);
insert into groups_tbl values (3, 'B', 3);
so the final result based on the data should look like this:
group_type email
-------- ------
A mike@yahoo.com
A sam@yahoo.com
B peter@yahoo.com
uj5u.com熱心網友回復：
users_tbl之間的簡單連接groups_tbl應該可以完成作業：
select group_type, email
from users_tbl u inner join groups_tbl g
on u.id = g.user_id
小提琴
uj5u.com熱心網友回復：
我不確定......可能是這樣的：
select g1.group_type, u1.email
from
(select distinct g.group_type
from members_tbl m,
users_tbl u,
groups g
where m.request_id = 123
and m.email = u.email
and u.group_id = g.group_id) x,
users u1,
groups g1
where g1.group_type = x.group_type
and g1.user_id = u1.user_id
或者，如果您想在 members_tbl 中輸出所有請求的結果，只需注釋第一個謂詞：
select g1.group_type, u1.email
from
(select distinct g.group_type
from members_tbl m,
users_tbl u,
groups g
where /*m.request_id = 123
and*/ m.email = u.email
and u.group_id = g.group_id) x,
users u1,
groups g1
where g1.group_type = x.group_type
and g1.user_id = u1.user_id
uj5u.com熱心網友回復：
select b.group_type,a.email from users a,groups b
where a.user_id = b.user_id
and b.group_type in (select b.group_type from members x,groups y, users z
where z.user_id = y.user_id and z.email = x.email and x.request_id = 123)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/430686.html
標籤：甲骨文
下一篇：如果我使用大量會話，記憶體溢位