|
|
|
Recursive Queries are useful for querying hierarchical data such as tree structured data using a common table expression (CTE). A CTE can be called repeatedly (recursively), fetching a subset of the results each time until it has built an entire result set. A good example is the way a hierarchy, such as a family tree, can be extracted from a database by recursively calling a CTE that gets all children of a specified parent. They are also a useful technique to "flatten" data from multi-row queries.
Example 1: Count Items
This example shows a way to count items:
WITH RECURSIVE counter (n) AS (
SELECT 1 as n
UNION ALL
SELECT n + 1 FROM counter WHERE n < 10
)
SELECT * from counter;
The output from this example would be:
n|
1|
2|
3|
4|
5|
6|
7|
8|
9|
10|
10 record(s) returned
Example 2:
CREATE TABLE testtable (id int, name char(200));
insert into testtable values (1,'First');
insert into testtable values (2, 'Second');
insert into testtable values (3, 'Third');
WITH RECURSIVE fn(id, name, n)
AS (
SELECT id, name, 1 as n from testtable where id = 1
UNION ALL
SELECT testtable.id, testtable.name, fn.n+1 from fn, testtable where testtable.id = fn.n+1
)
SELECT id, name FROM fn;
The output from this example would be:
id| name |
1| First |
2| Second |
3| Third |
Example 3: Employee Reporting "Tree"
This example builds an employee reporting "tree":
WITH RECURSIVE company_tree_list ( boss_id, emp_id, title, dept_id, level ) AS
(
–- Anchor member is defined.
SELECT emp.name, emp.emp_id, emp.title, edh.dep_id, 0 AS level
FROM employee_list AS emp
INNER JOIN emp_dept_history AS edh
ON e.emp_id, = edh.entity_id AND edh.enddate IS NULL
WHERE boss_id IS NULL
UNION ALL
–- Recursive member is defined referencing cte_name.
SELECT emp.boss_id, emp.emp_id, emp.title, edh.dep_id, level + 1
FROM employee_list AS e
INNER JOIN emp_dept_history AS edh
ON emp.emp_id = edh.entity_id AND edh.enddate IS NULL
INNER JOIN company_tree_list AS d
ON emp.boss_id = d.emp_id
)
SELECT name, emp_id, title, dept_id
FROM company_tree_list
INNER JOIN departments AS dept
ON company_tree_list.dept_id = dept.dept_id
WHERE dept.group = 'Sales' OR level = 0;
Example 4: Report a Category's Parent
create table "admin"."people" (
"id" integer,
"name" character(26),
"parent_id" integer
);
insert into "admin"."people" values('1','Root A ',NULL);
insert into "admin"."people" values('2','Root B ',NULL);
insert into "admin"."people" values('3','Child A1 ','1');
insert into "admin"."people" values('4','Child A2 ','1');
insert into "admin"."people" values('5','Child B1 ','2');
insert into "admin"."people" values('6','Child B2 ','2');
insert into "admin"."people" values('7','Grandchild A1a ','3');
insert into "admin"."people" values('8','Grandchild A1b ','3');
WITH RECURSIVE sub_tree (id, name, relative_depth ) AS (
SELECT id, name, 1 AS relative_depth
FROM people
WHERE name = 'Child A1'
UNION ALL
SELECT cat.id, cat.name, st.relative_depth + 1
FROM people cat, sub_tree st
WHERE cat.parent_id = st.id
)
SELECT * FROM sub_tree;
id | name | relative_depth |
3 | Child A1 | 1 |
7 | Grandchild A1a | 2 |
8 | Grandchild A1b | 2 |
3 record(s) returned
Example 5: Flatten Multiple Rows into a Single Field
create table mytest (
f1 varchar (20),
f2 integer);
insert into mytest values('aaa','1');
insert into mytest values('bbb','2');
insert into mytest values('ccccc','3');
insert into mytest values('ddd','4');
insert into mytest values('eeeeeeee','5');
insert into mytest values('f','6');
insert into mytest values('gggg','7');
insert into mytest values('hhh','8');
WITH RECURSIVE myflat ( flatf1, f2) AS
(
SELECT cast(f1 as varchar(8192)), f2
FROM mytest
where f2 = 1
ORDER BY f2
UNION ALL
SELECT flatf1 || f1, mytest.f2
FROM mytest, myflat parent
where parent.f2 + 1 = mytest.f2
)
SELECT top 1 flatf1
FROM myflat order by f2 desc;
aaabbbcccccdddeeeeeeeefgggghhh |
1 record(s) returned
