1 2 3 4 5 6 7 8 9 10 11 12
|
CREATE or replace VIEW init_recurs_join as
WITH recursive rec (gid, profondeur, code_zone, lettre_bv, fpkh, tpkh, id_nd_ini, id_nd_fin) AS (
SELECT gid, 1, code_zone, lettre_bv, fpkh, tpkh, id_nd_ini, id_nd_fin
FROM init_recurs_ord
union ALL
SELECT b.gid, a.profondeur +1, b.code_zone, b.lettre_bv, b.fpkh, b.tpkh, b.id_nd_ini, b.id_nd_fin
FROM init_recurs_ord b
INNER JOIN rec a ON b.id_nd_fin = a.id_nd_ini )
SELECT gid, profondeur, code_zone, lettre_bv, fpkh, tpkh, id_nd_ini, id_nd_fin,
row_number() over(partition BY profondeur ORDER BY gid desc) AS rnk_desc
FROM rec |
Partager