Suppliers(sid, sname, address)
Parts(pid, pname, colour)
Catalog(sid, pid, cost)
"找到所有供应每个零件的供应商"的答案是:
SELECT C.sid
FROM Catalog C
WHERE NOT EXISTS (
SELECT P.pid
FROM Parts P
WHERE NOT EXISTS (
SELECT C1.sid
FROM Catalog C1
WHERE C1.sid = C.sid
AND C1.pid = P.pid
)
)
这个答案的解释是什么?
我听说它被解释为"寻找不存在的供应商他们不出售的部分",但我正在努力了解以下内容如何实现这一目标。
SELECT C1.sid
FROM Catalog C1
WHERE C1.sid = C.sid
AND C1.pid = P.pid
如果目录是
James | Hammer
James | Anvil
James | Wrench
Henry | Hammer
Leroy | Anvil
和部分是
Hammer
Anvil
Wrench
那么在最里面的子句之后返回什么?
是吗
James | Hammer
James | Anvil
James | Wrench
Henry | Hammer
Leroy | Anvil
然后不存在使它如下吗?
James | --
Henry | Anvil, Wrench
Leroy | Hammer, Wrench
然后如何使用"部件"表减去这些值?
双重嵌套的NOT EXISTS
查询(不是真的,这就是我通常看到的它的名字),它专门用于回答这种类型的问题,即"所有 y 都有 x 真吗?
这是MySQL关于EXIST和NOT EXISTS的页面,其中特别提到了这种技术。
首先,在最里面的SELECT
查询中,您将选择每个商店携带的零件。 然后,使用第一个NOT EXISTS
子句,您将选择每个商店未运输的零件。 最后,在外部NOT EXISTS
子句中,您将选择为内部NOT EXISTS
子句返回空集的商店,这意味着它们承载每个部分。
下面是此查询的 SQLFiddle 操作。
警告:如果你正在使用SQL,那么在集合中思考和工作总是好的,而线性术语思考,比如接下来的事情,很快就会给你带来麻烦。 不要养成习惯!
但是,有时在尝试找出像这样的复杂查询时,将这些内容视为循环会有所帮助。
因此,以小提琴中的数据为例,我们有:
suppliers:
sid, name
9, 'AAA'
8, 'BBB'
7, 'CCC'
parts:
pid, name
1, 'wood'
2, 'stone'
3, 'paper'
catalog:
cid, pid, sid
1,1,9
2,2,9
3,1,8
4,1,7
5,2,7
6,3,7
因此,有了这些数据,AAA携带木材和石头,BBB只携带木材,CCC携带木材,石头和纸张。
现在,让我们逐行执行查询。 我们正在从 suppliers
中进行选择,并决定将哪些行包含在结果集中,因此请从 suppliers
中的第一行开始:9,'AAA'
。 我们将暂时S
此行调用。 仅当内部结果集中没有任何内容时,我们才会包含此行,因此让我们看一下。
suppliers:
sid, name
S => 9, 'AAA'
8, 'BBB'
7, 'CCC'
这个结果集正在从 parts
中选择,我们将逐行浏览它。 当我们这样做时,S
仍然等于9,'AAA'
。 所以从parts
的第一行开始:1,'wood'
。 我们现在将这一行称为P
。 如果下一级结果集中没有任何内容,我们只会在第一个内部结果集中包含此行,因此让我们移动到此处。 请记住,S
= 9,'AAA'
和 P
= 1,'wood'
.
suppliers:
sid, name
S => 9, 'AAA'
8, 'BBB'
7, 'CCC'
parts:
pid, name
P => 1, 'wood'
2, 'stone'
3, 'paper'
这个最里面的查询是从"目录"中选择的。我们正在寻找任何行,我们称之为 C
,在catalog
中,C.sid
等于 S.sid
U,C.pid
等于 P.pid
。 这意味着当前的供应商携带零件。 我们想要当前供应商不携带的零件,这就是我们反转结果集的原因。 我们知道S
的 sid 是 9,我们知道P
的 pid 是 1。 C
中是否有与此匹配的行? C
中的第一行与此匹配,因此我们知道此结果集不为空。
suppliers:
sid, name
S => 9, 'AAA'
8, 'BBB'
7, 'CCC'
parts:
pid, name
P => 1, 'wood'
2, 'stone'
3, 'paper'
catalog:
cid, pid, sid
C => 1,1,9 --Match found! Don't include P in outer result set
2,2,9
3,1,8
4,1,7
5,2,7
6,3,7
现在跳回到下一个最外层的循环。 内部结果集不为空,因此我们知道1,'wood
不会是此循环结果集的一部分。 所以我们在parts
中移动到下一行,2,'stone'
。 我们更新 P
的值以等于此行。 我们是否应该将此行包含在结果集中? 我们必须使用 P
的新值再次运行内部查询(S
仍然没有更改)。 因此,我们查找catalog
中sid
等于 9 且pid
等于 2 的任何行。 第二行匹配,因此有一个结果集。
suppliers:
sid, name
S => 9, 'AAA'
8, 'BBB'
7, 'CCC'
parts:
pid, name
1, 'wood'
P => 2, 'stone'
3, 'paper'
catalog:
cid, pid, sid
1,1,9
C => 2,2,9 --Match found! Don't include P in outer result set
3,1,8
4,1,7
5,2,7
6,3,7
跳回下一个最外层的循环。 内部结果集不为空,因此2,'stone'
不会成为此循环结果集的一部分。
当我们再次经历所有这些时 3,'paper'
,我们发现catalog
中没有行具有 sid = 9
和 pid = 3
. 最里面的结果集是空的,所以我们知道在下一个最外层的循环中包含这个值P
。
suppliers:
sid, name
S => 9, 'AAA'
8, 'BBB'
7, 'CCC'
parts:
pid, name
1, 'wood'
2, 'stone'
P => 3, 'paper'
catalog:
cid, pid, sid
1,1,9
2,2,9
3,1,8
4,1,7
5,2,7
6,3,7
C => --No match found, include P in outer result set
此时我们已经遍历了整个parts
表,因此我们为第二个循环设置了最终结果集,并且它不为空,这意味着我们找到了S
不携带的部分,因此我们知道我们不能在最终外循环的结果集中包含S
的当前值。
因此,我们继续suppliers
中的下一行并重新开始整个过程:
suppliers:
sid, name
9, 'AAA'
S => 8, 'BBB'
7, 'CCC'
一旦我们到达S = 7,'CCC'
我们将遍历所有这些循环,并且将在内部循环中找到每个提供的 P 值的匹配项,这意味着第二个循环将有一个空集。 我们找不到供应商不携带的任何部件,因此S
的值被添加到结果集中,这意味着它们承载了所有内容!