postgresql
Expressions de table communes (WITH)

Expressions de table communes dans les requêtes SELECT

Les expressions de table communes prennent en charge l'extraction de parties de requêtes plus importantes. Par exemple:

WITH sales AS (
  SELECT 
    orders.ordered_at,
    orders.user_id,
    SUM(orders.amount) AS total
  FROM orders
  GROUP BY orders.ordered_at, orders.user_id
)
SELECT 
  sales.ordered_at,
  sales.total,
  users.name
FROM sales 
JOIN users USING (user_id)

Traversée d'arbre utilisant WITH RECURSIVE

create table empl (
    name text primary key,
    boss text null
        references name 
            on update cascade 
            on delete cascade 
        default null
);

insert into empl values ('Paul',null);
insert into empl values ('Luke','Paul');
insert into empl values ('Kate','Paul');
insert into empl values ('Marge','Kate');
insert into empl values ('Edith','Kate');
insert into empl values ('Pam','Kate');
insert into empl values ('Carol','Luke');
insert into empl values ('John','Luke');
insert into empl values ('Jack','Carol');
insert into empl values ('Alex','Carol');

with recursive t(level,path,boss,name) as (
        select 0,name,boss,name from empl where boss is null
    union
        select
            level + 1,
            path || ' > ' || empl.name,
            empl.boss,
            empl.name 
        from 
            empl join t 
                on empl.boss = t.name
) select * from t order by path;