MySQL：SELECT EXISTS() AS 字段 WHERE 字段 = x

发布于 2024-10-27 08:30:45 字数 569 浏览 1 评论 0原文

问题：我有一个库存表和一个列出正在拍卖的物品的表。我想要一个别名字段（“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）有更好的方法来解决我的问题吗？

原文

The problem:
I have an inventory table, and a table listing items that are being auctioned off. I want to have an alias field ("isAuction") to represent whether or not an item with inventory's stock number is present in the auction items table.

I wrote the following Query:

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

This does populate "isAuction" field with 1 or 0, as required.

I now add a WHERE condition:

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

However, when I add a WHERE condition, I get an error:
"#1054 - Unknown column 'isAuction' in 'where clause'"

Two questions:
1) Where did I get it wrong?
2) Is there a better way to solve my problem?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

流云如水 2024-11-03 08:30:45

尝试HAVING isAuction = 1。

我在某处读到，HAVING 比 WHERE 更了解计算列。

回复收藏 0 原文

小嗷兮 2024-11-03 08:30:45

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

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

回复收藏 0 原文

为你鎻心 2024-11-03 08:30:45

您不能在 WHERE 中使用别名。你可以这样做

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

或者你可以将一个条件放入HAVING子句中（mysql允许你使用HAVING而不需要聚合函数）：
拥有 isAuction = 1
另外，你可以写

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

)a
WHERE a.isAuction = 1

You cannot use aliases in WHERE. You can do

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

Or you can put a condition into HAVING clause (mysql lets you use HAVING without aggregate functions) :
HAVING isAuction = 1
Also, you can write

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

)a
WHERE a.isAuction = 1

回复收藏 0 原文

~没有更多了~