select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 ligne)
create table geants(idg smallint, nmg character varying, actif boolean, idg_chef smallint);
CREATE TABLE
insert into geants(idg, nmg, actif, idg_chef) values
(1, 'Oumpfor', true, null),
(2, 'Galdor', false, 7),
(3, 'Goldar', false, 7),
(4, 'Goldur', true, 7),
(5, 'Guldor', false, 7),
(6, 'Geldor', false, 7),
(7, 'Golder', false, 8),
(8, 'Gildir', true, 1),
(9, 'Ambact', true, 1),
(10, 'Amdas', false, 1),
(11, 'Baldor', false, 8),
(12, 'Boldar', false, 8),
(13, 'Boldur', false, 8),
(14, 'Buldor', false, 8),
(15, 'Daldor', false, 16),
(16, 'Doldar', true, 18),
(17, 'Doldur', false, 18),
(18, 'Duldor', false, 1),
(19, 'Faldor', false, 20),
(20, 'Foldar', false, 21),
(21, 'Foldur', false, 1),
(22, 'Haldor', false, 23),
(23, 'Holdar', true, 24),
(24, 'Holdur', true, 25),
(25, 'Huldor', false, 26),
(26, 'Heldor', false, 8),
(27, 'Caldor', false, 28),
(28, 'Coldar', false, 31),
(29, 'Coldur', false, 30),
(30, 'Culdor', false, 31),
(31, 'Celdor', false, 1),
(32, 'Polder', false, 33),
(33, 'Pildir', false, 1),
(34, 'Faldor', false, 37),
(35, 'Foldar', false, 37),
(36, 'Foldur', false, 37),
(37, 'Fuldor', false, 1),
(38, 'Fuldor', false, 39),
(39, 'Fuldor', false, 40),
(40, 'Fuldor', false, 38);
INSERT 0 40
WITH RECURSIVE hierarchie_clan_asc(idg, idg_chef, ischef) AS (
SELECT idg, idg_chef, false
FROM geants
where idg_chef is not null
UNION ALL
SELECT g.idg, g.idg_chef, true
FROM hierarchie_clan_asc AS h join geants AS g on (h.idg_chef = g.idg)
),
hierarchie_clan_desc(idg, nmg, actif, idg_chef, nmg_chef, niveau) AS (
SELECT idg, nmg, actif, null::smallint, null::text, 1
FROM geants
where idg_chef is null
UNION ALL
SELECT g.idg, g.nmg, g.actif, h.idg, h.nmg, h.niveau+1
FROM hierarchie_clan_desc AS h join geants AS g on (h.idg = g.idg_chef)
),
grand_chef(idg) as (select idg from geants where idg_chef is null),
gardiens(idg) as ((select idg from geants where idg_chef = (select idg from grand_chef)) except (select idg_chef from geants)),
grands_mastards(idg) as (select h.idg from (select idg, idg_chef from hierarchie_clan_asc where ischef is true) h join grand_chef gc on (h.idg_chef = gc.idg) group by h.idg having count(*) >= 3),
mastards(idg) as ((select idg_chef from geants where idg_chef is not null) except (select idg from grands_mastards))-- ,
-- paltoquets(idg) as (select idg from geants except (select idg from grand_chef union all select idg from gardiens union all select idg from grands_mastards union all select idg from mastards))
SELECT
idg,
nmg,
nmg_chef,
actif,
case
when idg = (select idg from grand_chef) then 'GRAND CHEF'
when idg in (select idg from gardiens) then 'GARDIEN'
when idg in (select idg from grands_mastards) then 'GRAND MASTARD'
when idg in (select idg from mastards) then 'MASTARD'
-- when idg in (select idg from paltoquets) then 'PALTOQUET'
-- else 'RANG INCONNU'
else 'PALTOQUET'
end as rang,
niveau
FROM
hierarchie_clan_desc
order by idg asc
;
...........
ON ATTEND !
...........
Requête d'annulation envoyée
ERREUR: annulation de la requête à la demande de lutilisateur