sql - 选择列表中的列无效，因为它未包含在聚合函数或 GROUP BY 子句中

Question

我正在尝试返回一个表，其中包含使用嵌套集模型表示的层次结构中节点的深度，我正在按照本教程进行操作，但“查找节点深度”部分中使用的查询没有为我工作:http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

SELECT node.GroupName, (COUNT(parent.GroupName) - 1) AS depth
FROM CompanyGroup AS node,
        CompanyGroup AS parent
WHERE node.LeftID BETWEEN parent.LeftID AND parent.RightID
GROUP BY node.GroupName
ORDER BY node.LeftID;

运行此查询时出现错误“列‘CompanyGroup.GroupName’在选择列表中无效，因为它既未包含在聚合函数中也未包含在 GROUP BY 子句中。”

有人能解释一下为什么吗？

编辑:错误消息中的错误列，我很抱歉错误是:“列“CompanyGroup.LeftID”无效...”

Answer 1

试试这个 -

SELECT 
      node.GroupName
    , depth = COUNT(parent.GroupName) - 1
FROM CompanyGroup node
JOIN CompanyGroup parent ON node.LeftID BETWEEN parent.LeftID AND parent.RightID
GROUP BY node.GroupName
ORDER BY MIN(node.LeftID) --<--

或者试试这个——

SELECT 
      node.GroupName
    , depth = COUNT(parent.GroupName) - 1
FROM CompanyGroup node
JOIN CompanyGroup parent ON node.LeftID BETWEEN parent.LeftID AND parent.RightID
GROUP BY node.GroupName, node.LeftID
ORDER BY node.LeftID