MySQL 相关子查询：子查询无法从外部查询中找到表？

发布于 2024-09-11 21:42:07 字数 488 浏览 1 评论 0原文

自从我使用相关子查询以来已经有一段时间了，我不确定我这样做是否正确。在我的子查询第二行中，我试图从外表获取node.id。当我尝试执行查询时，我得到

错误代码：1054 未知列 “where 子句”中的“node.id”）

select node.id, node.title, depthLookup.depth
from posts node, (
    select count(parent.title) as depth
    from posts parent, posts children
    where children.lft > parent.lft 
    and children.rgt < parent.rgt
    and children.id = node.id
    order by parent.lft
) as depthLookup;

原文

its been sometime since i used corelated subqueries, i am not sure if i am doing this right. in my subquery 2nd last line, i am trying to get node.id from the outer table. when i try executing the query, i get

Error Code: 1054 Unknown column
'node.id' in 'where clause')

select node.id, node.title, depthLookup.depth
from posts node, (
    select count(parent.title) as depth
    from posts parent, posts children
    where children.lft > parent.lft 
    and children.rgt < parent.rgt
    and children.id = node.id
    order by parent.lft
) as depthLookup;

分享到QQ

分享到微博

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

发布评论

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

你爱我像她 2024-09-18 21:42:07

看来您只需将表达式从子句“from”移至字段列表

select node.id, node.title, 
(
    select count(parent.title) as depth
    from posts parent, posts children
    where children.lft > parent.lft 
    and children.rgt < parent.rgt
    and children.id = node.id
    order by parent.lft
) as depthLookup
from posts node;

或使用单值表，例如：

select node.id, node.title, depthLookup.depth
from posts node,
(
    select count(parent.title) as depth
    from posts parent, posts children
    where children.lft > parent.lft 
    and children.rgt < parent.rgt
    and children.id = node.id
    order by parent.lft
) as depthLookup;

It seems you just need to move your expression from clause 'from' to field list

select node.id, node.title, 
(
    select count(parent.title) as depth
    from posts parent, posts children
    where children.lft > parent.lft 
    and children.rgt < parent.rgt
    and children.id = node.id
    order by parent.lft
) as depthLookup
from posts node;

Or use single-value table like:

select node.id, node.title, depthLookup.depth
from posts node,
(
    select count(parent.title) as depth
    from posts parent, posts children
    where children.lft > parent.lft 
    and children.rgt < parent.rgt
    and children.id = node.id
    order by parent.lft
) as depthLookup;

回复收藏 0 原文

~没有更多了~