MySQL:SELECT EXISTS() AS field WHERE field = x

Question

问题:我有一张库存表，还有一张列出正在拍卖的元素的表格。我想要一个别名字段(“isAuction”)来表示具有库存库存编号的项目是否存在于拍卖项目表中。

我写了以下查询:

SELECT FROM inventory AS i
EXISTS(SELECT * FROM auctionitems AS a WHERE a.stockNo = i.stockNo) AS "isAuction" 

这会根据需要用 1 或 0 填充“isAuction”字段。

我现在添加一个 WHERE 条件:

SELECT FROM inventory AS i
EXISTS(SELECT * FROM auctionitems AS a WHERE a.stockNo = i.stockNo) AS "isAuction" 
WHERE isAuction = 1

但是，当我添加 WHERE 条件时，出现错误:“#1054 - ‘where 子句’中的未知列‘isAuction’”

两个问题:1)我哪里弄错了？2) 有没有更好的方法来解决我的问题？

Answer 1

尝试HAVING isAuction = 1。

我在某处读到 HAVING 比 WHERE 更清楚计算列。