Рекурсия

print(factorial(3))

drop table if exists employees;

create table employees (

employee_id serial primary key,

full_name VARCHAR not null,

manager_id INT

);

INSERT INTO employees (

employee_id,

full_name,

manager_id

)

VALUES

(1, 'Michael North', NULL),

(2, 'Megan Berry', 1),

(3, 'Sarah Berry', 1),

(4, 'Zoe Black', 1),

(5, 'Tim James', 1),

(6, 'Bella Tucker', 2),

(7, 'Ryan Metcalfe', 2),

(8, 'Max Mills', 2),

(9, 'Benjamin Glover', 2),

(10, 'Carolyn Henderson', 3),

(11, 'Nicola Kelly', 3),

(12, 'Alexandra Climo', 3),

(13, 'Dominic King', 3),

(14, 'Leonard Gray', 4),

(15, 'Eric Rampling', 4),

(16, 'Piers Paige', 7),

(17, 'Ryan Henderson', 7),

(18, 'Frank Tucker', 8),

(19, 'Nathan Ferguson', 8),

(20, 'Kevin Rampling', 8);

select * from employees;

with recursive subordinates as (

select 

e.employee_id,

e.full_name

from 

employees e 

where 

e.employee_id = 2

union

select 

e.employee_id,

e.full_name

from 

subordinates s

join employees e 

on s.employee_id = e.manager_id

)

select 

*

from 

subordinates

with recursive managers as (

select

e.employee_id,

e.full_name,

e.manager_id

from 

employees e 

where 

e.employee_id = 18

union all

select 

e.employee_id,

e.full_name,

e.manager_id

from 

managers m

join employees e

on e.employee_id = m.manager_id

)

select 

*

from 

managers;

-- 1 этап

select

e.employee_id,

e.full_name,

e.manager_id

from 

employees e 

where 

e.employee_id = 18

-- результат выполнения запроса

-- 18 Frank Tucker 8

-- общий результат выполнения cte 

-- 18 Frank Tucker 8

-- содержимое managers

-- 18 Frank Tucker 8

-- 2 этап

select 

e.employee_id,

e.full_name,

e.manager_id

from 

managers m

join employees e

on e.employee_id = m.manager_id

-- результат выполнения запроса

-- 8 Max Mills 2

-- общий результат выполнения cte 

-- 18 Frank Tucker 8

-- 8 Max Mills 2

-- содержимое managers

-- 8 Max Mills 2

-- 3 этап

select 

e.employee_id,

e.full_name,

e.manager_id

from 

managers m

join employees e

on e.employee_id = m.manager_id

-- результат выполнения запроса

-- 2 Megan Berry 1

-- общий результат выполнения cte 

-- 18 Frank Tucker 8

-- 8 Max Mills 2

-- 2 Megan Berry 1

-- содержимое managers

-- 2 Megan Berry 1

-- 4 этап

select 

e.employee_id,

e.full_name,

e.manager_id

from 

managers m

join employees e

on e.employee_id = m.manager_id

-- результат выполнения запроса

-- 1 Michael North

-- общий результат выполнения cte 

-- 18 Frank Tucker 8

-- 8 Max Mills 2

-- 2 Megan Berry 1

-- 1 Michael North

-- содержимое managers

-- 1 Michael North

-- 5 этап

select 

e.employee_id,

e.full_name,

e.manager_id

from 

managers m

join employees e

on e.employee_id = m.manager_id

-- результат выполнения запроса

-- общий результат выполнения cte 

-- 18 Frank Tucker 8

-- 8 Max Mills 2

-- 2 Megan Berry 1

-- 1 Michael North

with recursive factorial as (

select 

1 as n,

1 as res

union 

select

f.n + 1 as n,

f.res * (f.n + 1) as res

from 

factorial f

where 

f.n + 1 <= 5

)

select

*

from 

factorial;

with recursive factorial as (

select 

5 as n,

5 as res

union 

select

f.n - 1 as n,

f.res * (f.n - 1) as res

from 

factorial f

where 

f.n - 1 >= 1

)

select

*

from 

factorial

where n = 1;