mysql - 如何在 MySQL 中对 "AS"值执行 SELECT。

Question

我在下面有这个查询。它提供类别的名称及其在层次结构中的深度。深度是一个“AS”值。但是，我希望能够只选择那些具有特定深度的名称。即深度= 3。我用谷歌搜索到死，并尝试创建一个虚拟列，但我不太擅长sql。有什么建议吗？谢谢!

SELECT node.name, (COUNT(parent.name) - 1) AS depth
    FROM nested_categories AS node,
    nested_categories AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    GROUP BY node.name
    ORDER BY depth

Answer 1

您需要使用 HAVING 子句。这很像 WHERE 子句，但它是在过程的后期计算的，因此可以在聚合列上工作:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_categories AS node,
nested_categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
HAVING depth = 3
ORDER BY depth

根据您的数据库引擎，您可能需要将其编写为:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_categories AS node,
nested_categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
HAVING (COUNT(parent.name) - 1) = 3
ORDER BY depth