如何使用条件内子查询结果的字段？
这是我尝试执行的查询，它应该返回一个包含未满池数据的表（members_nr
SELECT id, name,
(
SELECT COUNT(*) FROM pools_entries WHERE pool_id=p.id AND pending=0
) AS members_nr,
members_max, open
FROM pools p
WHERE id IN(1,2,3,4) AND members_nr < members_max;
问题是 MySQL 不会将members_nr 识别为字段，因为它是子查询的结果。这个小问题有逻辑解决方案吗？
任何帮助将不胜感激:)
Here is the query I'm trying to execute, and it's supposed to return a table containing data for the pools that are not full (members_nr < members_max).
SELECT id, name,
(
SELECT COUNT(*) FROM pools_entries WHERE pool_id=p.id AND pending=0
) AS members_nr,
members_max, open
FROM pools p
WHERE id IN(1,2,3,4) AND members_nr < members_max;
The problem is MySQL won't recognize members_nr as a field since it's a result from a subquery. Is there a logic solution to this little issue?
Any help will be much appreciated :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
注意是正确的，您需要having 子句。但为了谷歌用户的利益，我将分享一些知识。
WHERE 子句用于将结果集限制为特定记录，它也用于优化。 Mysql 使用 WHERE 子句来标识它可以使用哪个索引来加速查询。
HAVING 子句在查询末尾执行。它用于过滤记录集。假设您有一个来自数据库的与 WHERE 子句匹配的内容列表。然后，您可以使用 HAVING 根据某些设定条件进一步过滤该列表。
我的基本经验法则是：如果您需要根据列的值进行选择，请使用 WHERE，如果您需要根据表中非列的值进行选择，请使用 HAVING。
N.B. is correct, you need the having clause. But for the sake of the googler's i'll share a little knowledge.
The WHERE clause is used for restricting the resultset to specific records, it is also used for optimisation. Mysql uses the WHERE clause to identify which index's it can use to speed up the query.
The HAVING clause is executed right at the end of the query. It is used for filtering the recordset. So imagine you have a list of stuff from the database that matches your WHERE clause. You can then use HAVING to filter that list down further on some set conditions.
My basic rule of thumb is: if you need to select based on a column's value, use WHERE, if you need to select based on the value of something which is not a column in the table, use HAVING.