非常感谢您的阅读。
假设以下表格摘录(供应商-零件SP)
------------------------
S# | P# | QTY
------------------------
S1 | P1 | 30
S1 | P2 | 35
S1 | P3 | 40
S2 | P1 | 100
S2 | P2 | 50
S3 | P2 | 30
S4 | P1 | 40
S4 | P2 | 35
我有兴趣了解如何形成查询,最好是基于关于并集、交集、差异、产品、自然连接、分割等操作,这些操作将返回,比如说供应S2供应的所有零件的供应商编号。
我知道如果我使用:
SP [S# , P#] DIVIDEBY (SP WHERE S# = 'S2') [P#]
我将获得供应商编号,这些供应商至少像S2那样提供所有这些零件,但我无法想象如何形成查询,只返回与供应商S2完全相同的零件的供应商编号。
这个问题是基于个人的科学兴趣。
如果这有帮助,请告诉我。我正在使用自联接。
select *
from infoz as t1
inner join infoz as t2
on t1.P# = t2.P#
where t1.S# = 's2'
让我们筛选此信息。。。
select t2.S#, COUNT(t2.P#) as cnt
from infoz as t1
inner join infoz as t2
on t1.P# = t2.P#
where t1.S# = 's2'
group by t2.S#
现在,你可以在这里看到S2的计数,并通过它进行过滤。
select *
from
(
select t2.S#, COUNT(t2.P#) as cnt
from infoz as t1
inner join infoz as t2
on t1.P# = t2.P#
where t1.S# = 's2'
group by t2.S#
) as r1
where r1.cnt = (select count(t.P#)
from infoz as t
where t.S# = 's2') and r1.S# ! = 's2'