SQL：递归选择所有子记录的 SUM
我有一张与其自身具有一对多关系的表。每个记录可以有 n 个来自同一个表的子记录。例如
create table folder
ID: Number 20 PK
PARENT_ID: Number 20 FK references folder.ID
SIZE: NUMBER 20
...
给定一个 ID，我想选择所有文件夹记录的 SUM(SIZE) 递归地。目标数据库是MySql 5，但如果是就好了 如果它足够通用，可以在 Oracle 和 MS-SQL 中工作。
我不知道树有多深，可能是 1 级，可能是 50（或更多）
I have a table that has a one to many relationship with itself. Each record can have n number of children from that same table. For example
create table folder
ID: Number 20 PK
PARENT_ID: Number 20 FK references folder.ID
SIZE: NUMBER 20
...
Given an ID, I want to select the SUM(SIZE) of all folder records
recursively. The target database is MySql 5, but it would be nice
if it was generic enough to work in Oracle and MS-SQL as well.
I won't know how deep the tree is, could be 1 level, could be 50 (or more)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这可能是一些帮助：http://mikehillyer.com/articles/ management-hierarchical-data-in-mysql/
这将是 Oracle 中的一个简单查询（http://download-east.oracle.com/docs/cd/B12037_01/server.101/b10759/queries003.htm），因为它支持分层查询使用“CONNECT BY”，但我认为 MySQL 没有类似的解决方案。看起来您将要做一些非常低效的事情，或者您将不得不修改表结构以支持此特定功能。
This may be some some assistance: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
This would be a simple query in Oracle ( http://download-east.oracle.com/docs/cd/B12037_01/server.101/b10759/queries003.htm) since it supports hierarchical queries using "CONNECT BY" but I don't think there's a comparable solution for MySQL. It looks like you're going to do something really inefficient or you're going to have to modify your table structure to support this specific function.
一种解决方案是向表“topmost_parent”添加一列并加入该列。
One solution would be to add a column to the table "topmost_parent" and join on that.
您应该考虑使用嵌套集模型重新构造数据。以下链接描述了如何执行此操作：
http:// /www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql
You should consider re-structuring the data using a nested set model. The following link describes how to do it:
http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql