Menu
I have a table similar to this:
CREATE TABLE example ( id integer primary key, name char(200), parentid integer, value integer); I can use the parentid field to arrange data into a tree structure.
Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?
UPDATE: I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.
BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!
420
First, specify the name of the view that you want to create in the CREATE RECURSIVE VIEW clause. You can add an optional schema-qualified to the name of the view. Second, add the SELECT statement to query data from base tables. The SELECT statement references the view_name to make the view recursive.
Recursion occurs because of the query referencing the CTE itself based on the Employee in the Managers CTE as input. The join then returns the employees who have their managers as the previous record returned by the recursive query. The recursive query is repeated until it returns an empty result set.
AS ( subquery ) Select col1, col2, .. from cte_name; cte_name: Name given to recursive subquery written in subquery block. col1, col2, ... colN: The name given to columns generated by subquery. subquery: A MySql query that refer to itself using cte_name as its own name.
There is no absolute reason why recursive queries should perform badly, just because they are recursive. What usually happens is that recursive queries get more expensive against larger data sets than a non-recursive query against a table of a similar size.
Here is an example script using common table expression:
with recursive sumthis(id, val) as ( select id, value from example where id = :selectedid union all select C.id, C.value from sumthis P inner join example C on P.id = C.parentid ) select sum(val) from sumthis The script above creates a 'virtual' table called sumthis that has columns id and val. It is defined as the result of two selects merged with union all.
First select gets the root (where id = :selectedid).
Second select follows the children of the previous results iteratively until there is nothing to return.
The end result can then be processed like a normal table. In this case the val column is summed.
104
Since version 8.4, PostgreSQL has recursive query support for common table expressions using the SQL standard WITH syntax.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
