SQL:递归选择所有子记录的SUM [英] SQL: Select SUM of all children records recursively

问题描述

我有一个表，它与自己具有一对多的关系.每个记录可以有来自同一张表的n个孩子.例如

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
...

给出一个ID，我想选择所有文件夹记录的SUM(SIZE) 递归地目标数据库是MySql 5，但是会很好 如果它足够通用，也可以在Oracle和MS-SQL中使用.

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.

我不知道树有多深，可能是1级，可能是50级(或更多)

I won't know how deep the tree is, could be 1 level, could be 50 (or more)

推荐答案

这可能会有所帮助: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

这将是Oracle中的简单查询(

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.

这篇关于SQL:递归选择所有子记录的SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！