子查询在SQLite查询不工作

问题描述

我使用嵌套集模型在iPhone上的本地SQLite数据库中存储大层数据。我从他们的网站上阅读了，了解如何做这个，但是他们建议（和我需要）的一个查询似乎不与SQLite工作，我不知道如何解决它。

I am using the nested set model to store a large hierarchy of data in a local SQLite database on an iPhone. I read the MySQL tech article from their web site on how to do this, but one of the queries they suggest (and that I need) doesn't appear to work with SQLite and I'm not sure how to get around it.

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM line_items AS node,
    line_items AS parent,
    line_items AS sub_parent,
    (SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM line_items AS node,
        line_items AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'Power Up'
        GROUP BY node.name
        ORDER BY node.lft
    ) AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

SQLite报告 sub_tree.name isn' t列，我假设这是因为它的子查询实现是不完整的。

SQLite reports that sub_tree.name isn't a column, and I assume this is because its subquery implementation is incomplete. Does anyone have any ideas about how to get around this limitation?

查询的目的是获取给定父节点的所有直接子节点。

The purpose of the query is to get all the immediate children of a given parent node.

推荐答案

尝试在子查询中使用node.name AS name，即

Try using "node.name AS name" in the subquery, i.e.

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM line_items AS node,
    line_items AS parent,
    line_items AS sub_parent,
    (SELECT node.name AS name, (COUNT(parent.name) - 1) AS depth
        FROM line_items AS node,
        line_items AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'Power Up'
        GROUP BY node.name
        ORDER BY node.lft
    ) AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

至少似乎摆脱了错误。

这篇关于子查询在SQLite查询不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！