java找出价格最低_SQL查询查找每个零件的最低价格的供应商

我需要找到每个零件价格最低的供应商。

表：供应商(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”是连接的一部分，因此它也可以到达那里。我也没有包含表别名，因为虽然这是最佳实践，但不是必需的。