我需要找到每个零件价格最低的供应商。
表:供应商(sid、sname、地址)、零件(pid、pname、颜色)、目录(sid、pid、成本)
这是有效的:SELECT
sname, pid
FROM
(SELECT
*
FROM
suppliers
NATURAL JOIN catalog
NATURAL JOIN (SELECT
pid, MIN(cost) AS min_cost
FROM
catalog
GROUP BY (pid)) AS m
HAVING cost = min_cost) AS n
但是,当我试图将其缩短为以下值时,我得到一个错误,即having子句中存在未知的成本:
SELECT
sname, pid
FROM
suppliers
NATURAL JOIN
catalog
NATURAL JOIN
(SELECT
pid, MIN(cost) AS min_cost
FROM
catalog
GROUP BY (pid)) AS m
HAVING cost = min_cost
为什么找不到成本?表中的成本不是因为我已将子查询加入目录吗?
编辑
根据建议,我将其改为使用INNER JOIN,而不是NATURAL JOIN,但仍然会出现相同的错误。新查询:
SELECT
s.sname, m.pid
FROM
suppliers s
INNER JOIN
catalog c ON s.sid = c.sid
INNER JOIN
(SELECT
pid, MIN(cost) AS min_cost
FROM
catalog
GROUP BY (pid)) AS m ON c.pid = m.pid
HAVING cost = min_cost
编辑2
问题不是JOIN而是HAVING,正如bbrumm的回答所示,实际上应该是WHERE。
最佳答案:
我建议这样一个问题:SELECT
supplier.sname,
catalog.pid
FROM suppliers
INNER JOIN catalog ON suppliers.supplier_id = catalog.supplier_id
INNER JOIN
(SELECT
pid, MIN(cost) AS min_cost
FROM catalog
GROUP BY (pid)) AS m
ON catalog.pid = m.pid
WHERE catalog.cost = m.min_cost;
我对您的列名(如供应商id)做了一些假设,您可能需要更改。可以指出,“cost=min_cost”是连接的一部分,因此它也可以到达那里。我也没有包含表别名,因为虽然这是最佳实践,但不是必需的。