Complex aggregate functions and hierarchical structures in SQL

Question

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?

Answer 1

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.