SQL:语法错误与相交? [英] SQL: Syntax error with intersect?

问题描述

这是我的查询:

-- Sids of suppliers who supply a green part AND a red part
(SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color = "red")
INTERSECT
(SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color = "green");

这是错误:

错误1064(42000):您有一个错误 在您的SQL语法中；查看手册 对应于您的MySQL服务器 使用正确语法的版本 靠近 相交 (选择Suppliers.sid 来自供应商 在第6行上，在目录.sid = Sup上加入目录.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "INTERSECT (SELECT Suppliers.sid FROM Suppliers JOIN Catalog ON Catalog.sid = Sup" on line 6.

我在做什么错了?

这是架构:

供应商( sid:整数，sname:字符串，地址字符串)

Suppliers(sid: integer, sname: string, address string)

Parts( pid:整数，pname:字符串，颜色:字符串)

Parts(pid: integer, pname: string, color: string)

目录( sid:整数，pid:整数，成本:实数)

粗体 =主键

推荐答案

您似乎正在使用的MySQL不支持INTERSECT语法.您将不得不以另一种方式解决它.

MySQL, which you appear to be using, does not support the INTERSECT syntax. You're going to have to solve it another way.

在这种情况下，这是微不足道的-我们只需要列出提供某些部分绿色"和红色"的所有供应商的列表，您的查询就不会打扰到这些部分本身是否相关，因此我们可以解决这样很容易:

In this case, it is trivial -we only need a list of all suppliers that offer "green" and "red" of some part- your query does not bother to see if the parts themselves are related, so we can solve it quite easily like this:

SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color IN ('red', 'green')
GROUP BY Suppliers.sid
HAVING COUNT(DISTINCT Parts.color) = 2

就个人而言，我不认为原始查询是典型的INTERSECT问题.看一看Vinko Vrsalovic提供的JOIN解决方案，该解决方案可以模拟INTERSECT(即使RDBMS实际上实际上会提供INTERSECT，我还是会更喜欢它).

Personally, I don't believe the original query is a typical INTERSECT problem. Take a look at the JOIN solution offered by Vinko Vrsalovic for a general solution to emulate the INTERSECT (which I would btw prefer even if the RDBMS would in fact offer INTERSECT natively).

这篇关于SQL:语法错误与相交?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！