SQL 中复杂的聚合函数和层次结构
我想在 SQL 数据库（MySQL，但想避免 DBMS 特定的功能）中存储任意深度的树结构。现在我想计算每个节点的值N，如下所示：
- 首先，计算当前节点的所有（直接）子节点（即没有子节点）中某一列的总和，
- 然后，计算剩余每个子节点（即有子节点的子节点）的最大值N
- ，最后将前两步的结果相加得到N的值当前节点
显然，这涉及递归，因此嵌套集似乎是此场景的选择表示。但是，我不知道如何将上面的计算公式化为 SQL 查询。获得所有后代的 SUM() 或 MAX() 很容易，但聚合函数的组合方式使问题变得非常复杂。有人有解决办法吗？
I want to store a tree structure of arbitrary depth in an SQL database (MySQL, but want to avoid DBMS-specific features). Now I want to compute a value N for each node as follows:
- first, calculate the sum of a certain column in all (immediate) children of the current node which are leaves (i.e. have no children)
- then, calculate the maximum of the values N of each of the remaining children, i.e. those with children
- finally, add the results of the first two steps to get the value N for the current node
Obviously, this involves a recursion, so nested sets seem to be the representation of choice for this scenario. However, I couldn't work out how to formulate the calculation above as an SQL query. It is easy to get the SUM() or MAX() of all the descendants, but the way the aggregate functions are combined complicates the matter greatly. Does anyone have a solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果模式还有其他计算列（例如深度和“是叶节点”）怎么办？
它需要更多的维护（并不是说嵌套集是免维护的），但我认为它使用“标准”SQL 可以实现上述查询。
What if the schema also had additional computed columns such as depth and "is leaf node"?
It would require more maintenance (not that nested sets are maintenance free) but I think it puts the above query within reach using "standard" SQL.