这个SQL查询有更好的写法吗？

Question

我有以下关系。

Suppliers( sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog( sid: integer, pid: integer, cost: real)

此问题要求我查找由Acme Widget Suppliers供应且没有其他人供应的零件的pnames。我编写了以下SQL语句；但是感觉这个查询由于重复而效率低下。我想知道是否有更好的方法来编写此查询，而无需重复选择catalog part。

Select P.pname 
FROM Parts P 
WHERE P.pid IN (
    Select C.pid 
    FROM Catalog C 
    INNER JOIN Supplier S
    ON S.sid = C.sid 
    WHERE S.sname = "Acme Widget Suppliers" 
    AND C.pid NOT IN (
            SELECT C2.pid 
            FROM Catalog C2
            INNER JOIN Supplier S
            ON S.sid = C2.sid 
            WHERE S.sname <> "Acme Widget Suppliers"
        )
);

- Guan Summy Huang

---

请明确当您说“仅限于Acme小部件供应商而无其他人”时，您的意思是仅需要Acme零件，还是既需要Acme零件，又需要由其他厂家生产的零件？ - Tim Biegeleisen

这个问题最多只能说是模糊的。pid 1和7都可以有相同的名称，比如Cracker Jacks。但pid在连接表中。 - Drew

这意味着我们需要找到仅由Acme Widget供应商提供的零件。如果该零件由Acme和另一家供应商提供，则不计入统计。 - Guan Summy Huang

按零件名称或 PID 进行翻译。在这里与我们合作。魔鬼藏在细节中，而它正在抬起它的刺角。 - Drew

按照问题所述，仅通过部件名称进行翻译。我并没有写这个问题。这是一个作业问题，我只想学习一种更优雅的解决方法。 - Guan Summy Huang

如果语义太难理解，我将跳过此问题。 - Drew

Answer 1

您是正确的。您想要进行的查询可以大大简化。请尝试以下内容。

SELECT P.pname FROM Parts P, Suppliers S, Catalog C
WHERE C.pid = P.pid
AND C.sid = S.sid
AND S.sname == "Acme Widget Suppliers"

- pmains

Answer 2

我在第二个部分使用了left join，因为我预计会得到nulls。在这种情况下，只有一个acme的行而没有其他行。 Sql Fiddle演示

Select *
FROM Parts P 
INNER JOIN Catalog C1
   ON P.pid = C1.pid
INNER JOIN Suppliers S1
   ON C1.sid = S1.sid
LEFT JOIN Catalog C2
   ON P.pid = C2.pid
  AND C1.sid <> C2.sid
WHERE 
   S1.sname = 'Acme' 
AND C2.sid IS NULL

- Juan Carlos Oropeza

---

我创建了一个fiddle并改进了我的查询。包括第二个join <> C1.sid，我确保加入了其他公司。如果该join为空，则完成。检查演示，删除最后一行and c2.sid is null，您将看到所有结果。 - Juan Carlos Oropeza

Answer 3

正确的查询应该是：

Select P.PName from Suppliers S1 
 join Catalog C1
  on S1.sid = C1.sid
  join parts P
   on P.pid = C1.pid
where  S1.sname = 'Acme Widget Suppliers'
 and not exists 
 ( select  1 from catalog C2 where C2.sid != C1.sid 
   and C2.pid = C1.pid)

not exist非常高效，因为它在找到第一个匹配项后就停止搜索。

SQL Fiddle

- dcieslak

Answer 4

我会尝试将三个表连接在一起，然后使用您已经使用的WHERE子句进行限制。我在SELECT语句中使用了DISTINCT来确保去除重复项（如果有的话）。

SELECT DISTINCT p.pname
FROM  Parts p INNER JOIN Catalog c
ON p.pid = c.pid
INNER JOIN Suppliers s
ON c.sid = s.sid
WHERE s.sname = "Acme Widget Suppliers"

- Tim Biegeleisen

---

问题中说“由Acme Widget供应商提供，没有其他人提供”，你没有“没有其他人”这部分。 - Kobi

我理解“no one else”这个短语是多余的，即“只由Acme Widget供应商提供”。我错了吗？ - Tim Biegeleisen

是的。汽车A由福特和雪佛兰销售。汽车B仅由道奇销售。您可以从OP查询中获得这种解释。 - Juan Carlos Oropeza

@Kobi：您不需要添加任何其他部分，因为您只从Acme Widget中选择项目。如果您添加一个类似的语句并且s.name <> 'Acme Widget'，就像是在说获取所有既不是奇数也不是偶数的偶数一样。 - TobiSH

@TobiSH - 请阅读Juan的评论，他非常好地解释了这一点。我们正在寻找Acme独家销售的零件。 - Kobi

Answer 5

这可以工作：

select p.pname from Catalog c
join Parts p on p.pid = c.pid
join Suppliers s on s.sid = c.sid
where s.sname = 'Acme Widget Suppliers'
and p.pid in (select pid as SupplierCount from Catalog c
              group by pid having count(*) = 1)

内部的select旨在从任何供应商中找到独家零件。如果一个零件由多个供应商销售，它将不会被包括在内。

工作示例：http://sqlfiddle.com/#!6/1ccde/10

- Kobi

---

这里对count的使用很好。这个解决方案非常容易理解 :) - Guan Summy Huang

谢谢！我不小心使用了TSQL（我读成了MSSQL而不是MySQL）。我参考了另一个代码片段，它仍然有效：http://sqlfiddle.com/#!9/895a49/9 - Kobi

Answer 6

为了提供另一种解决方法，这里使用数据分组的变体。这是一个解决方案，选择所有仅由“Acme Widget Suppliers”提供的pnames。

Select P.pname 
FROM Parts P 
INNER JOIN Catalog C on c.pid=p.pid
INNER JOIN Supplier S  ON S.sid = C.sid 
GROUP BY p.pname 
HAVING min(s.sname)=max(s.sname) 
   and max(s.name)='Acme Widget Suppliers'

这个查询按照零件名称分组，并计算供应商名称的 最小值 和 最大值。如果一个零件有不同的供应商，则 最小值 和 最大值 不相等。

由于我理解问题中的细微差别（是由“Acme”提供的零件还是只有由“Acme”提供）仍然不清楚，因此我将坚持选择较后且更困难的选项 ;）。

- wumpz

---

另一个出色的解决方案 :) 我从来没有想过。谢谢。 - Guan Summy Huang

Answer 7

根据您的查询，似乎不需要使用in和join语句，而是使用exists语句更好。

Select P.pname 
FROM Parts P 
WHERE EXISTS (
    Select C.pid 
    FROM Catalog C 
    INNER JOIN Supplier S
    ON S.sid = C.sid 
    WHERE P.pid = C.pid  AND  S.sname = "Acme Widget Suppliers" 
);

- Indra Prakash Tiwari

---

比“IN”更好，我同意，但你有什么依据说“EXISTS”比“JOIN”更好呢？ - Juan Carlos Oropeza