sql - 保持 SQL 干燥

Question

这是我正在运行的 MySQL 查询:

-- get the sid of every supplier who does not supply both a red and green part
SELECT Suppliers.sid, Parts.color
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Suppliers.sid NOT IN (
    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
)
ORDER BY Suppliers.sid DESC;

如您所见，这重复了两次:

FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid

如何才能只声明一次并引用两次？或者这是否表明我的整个查询存在某种缺陷？

当我使用相同的三行代码运行 15 个不同的查询时，问题会更加严重。

Answer 1

如果您要在整个系统中继续使用表的关系，那么您要查看的内容称为 view .