SELECT C.pid
FROM Catalog C, Suppliers S
WHERE S.sname = ‘Yosemite Sham’ AND C.sid = S.sid
AND C.cost ≥ ALL (Select C2.cost
FROM Catalog C2, Suppliers S2
WHERE S2.sname = ‘Yosemite Sham’ AND C2.sid = S2.sid)
SELECT C.pid
FROM Catalog C, Suppliers S
WHERE S.sname = ‘Yosemite Sham’ AND C.sid = S.sid
AND C.cost ≥ ALL (Select C2.cost
FROM Catalog C2, Suppliers S2
WHERE S2.sname = ‘Yosemite Sham’ AND C2.sid = S2.sid)
首先,学习使用正确、明确的JOIN
语法。
然后,如果您假设最大成本只会发生一次,可以执行以下操作:
SELECT C.pid
FROM Catalog C JOIN
Suppliers S
ON C.sid = S.sid
WHERE S.sname = 'Yosemite Sham'
ORDER BY c.Cost DESC
FETCH FIRST 1 ROW ONLY ;
SELECT C.pid
FROM Catalog C JOIN
Suppliers S
ON C.sid = S.sid LEFT JOIN
Catalog C2
ON C2.sid = C.sid AND
C2.Cost > C.Cost
WHERE S.sname = 'Yosemite Sham' AND c2.Cost IS NULL
ORDER BY c.Cost DESC;
sid
与sname
是一对一的。这是一种便利(很可能是真实的),有助于避免额外连接到Suppliers
。