执行简单查询的多种独特方式（ORACLE）？

I have to come up with 5 different ways (unique execution plans) to process the following query.

我必须提出5种不同的方法(独特的执行计划)来处理以下查询。

Find the items that are delivered by all suppliers.

查找所有供应商提供的项目。

My database holds the following tables:

我的数据库包含以下表格:

QSPL – it holds a list of supplier names

QSPL - 它包含供应商名称列表

SPLNO (number)
SPLNAME (varchar)

QDEL– it holds delivery items, suppliers, and departments

QDEL-它拥有交货项目,供应商和部门

DELNO (number)
DELQTY (number)
ITEMNAME (varchar)
DEPTNAME (varchar)
SPLNO (number)

QITEM – it holds list of items

QITEM - 它包含项目列表

ITEMNAME (varchar)
ITEMTYPE (varchar)
ITEMCOLOR (varchar)

I was able to successfully come up with the following four unique queries.

我能够成功地提出以下四个独特的查询。

select itemname --, etc.
from qitem
where itemname not in
(select itemname
from qitem, qspl
where (char(splno)+itemname) not in
(select char(splno)+itemname
from qdel));

select itemname --,etc.
from qitem
where not exists
    (select *
    from qspl
    where not exists
        (select *
from qdel
where qdel.itemname = qitem.itemname
and Qdel.splno = qspl.splno));

select a.itemname --, etc
from qitem a join qdel b on a.itemname = b.itemname
group by a.itemname
having count (distinct splno) = (select count(*) from qspl);

select itemname
from qdel
group by itemname
having count (distinct splno) = (select count(*) from qspl);

I have no idea what to do for a 5th unique query. Does anyone have a clue?

我不知道如何处理第5个唯一查询。有人有线索吗?

I tried to put this question in the best possible context with significant detail, feedback is greatly appreciated.

我试图将这个问题放在最佳可能的背景下,并提供重要的细节,非常感谢您的反馈。

Thanks

3 个解决方案

#1

Maybe some SQL 86 syntax:

也许一些SQL 86语法:

select a.itemname --, etc
from qitem a, qdel b 
where a.itemname = b.itemname
group by a.itemname
having count (distinct splno) = (select count(*) from qspl);

Or an outer join

或外连接

select a.itemname --, etc
from qspl s, qdel b 
WHERE s.splno (+)= b.splno
group by s.splno
having count (distinct b.splno) = (select count(*) from qspl);

#2

This is another unique way (which I'm sure it's horribly inefficient):

这是另一种独特的方式(我确信它的效率非常低):

select distinct splname
from (
  select qi.itemname, 
         qs.splname,
         count(distinct qi.itemname) over () as total_items,
         count(distinct qd.itemname) over (partition by qd.splno) as items_per_supp
  from qitem qi
    left join qdel qd on qi.itemname = qd.itemname
    left join qspl qs on qs.splno = qd.splno
) t
where total_items = items_per_supp

Or a variant of your #3 which will probably use a different execution plan:

或者#3的变体可能会使用不同的执行计划:

with supplier_items as (
  select splno, count(*) item_count
  from qdel 
  group by splno
)
select splname
from qspl qs
  join supplier_items si on qs.splno = si.splno
where si.item_count = (select count(*) from qitem);

#3

Since this is homework, I will be obtuse: Check out the Oracle MINUS operator.

由于这是作业,我将是迟钝的:检查Oracle MINUS运算符。

#1