目标是从

-- a → b → c → d
-- ↓ ↘   ↘
-- g   e → f
-- ↓
-- h
dag (vertex, successor) as (values
    ('alpha', 'bravo'),
    ('bravo', 'charlie'),
    ('charlie', 'delta'),
    ('delta', null),
    ('alpha', 'echo'),
    ('echo', 'foxtrot'),
    ('bravo', 'foxtrot'),
    ('foxtrot', null),
    ('alpha', 'golf'),
    ('golf', 'hotel'),
    ('hotel', null)
)

进入之内

'id_foo', 0, 'alpha'
'id_foo', 1, 'bravo'
'id_foo', 2, 'charlie'
'id_foo', 3, 'delta'
'id_bar', 0, 'alpha'
'id_bar', 1, 'bravo'
'id_bar', 2, 'foxtrot'
'id_qux', 0, 'alpha'
'id_qux', 1, 'echo'
'id_qux', 2, 'foxtrot'
'id_fno', 0, 'alpha'
'id_fno', 1, 'golf'
'id_fno', 2, 'hotel'

我可以分两步做。

with recursive intermediate as (
    select * from (
        with recursive
            dag (vertex, successor) as (values
                ('alpha', 'bravo'),
                ('bravo', 'charlie'),
                ('charlie', 'delta'),
                ('delta', null),
                ('alpha', 'echo'),
                ('echo', 'foxtrot'),
                ('bravo', 'foxtrot'),
                ('foxtrot', null),
                ('alpha', 'golf'),
                ('golf', 'hotel'),
                ('hotel', null)
            ),
            cte as (
                select
                        vertex,
                        successor,
                        1 as length,
                        vertex as path
                    from dag where successor is null
                union all
                select
                        dag.vertex,
                        dag.successor,
                        length + 1,
                        dag.vertex||'→'||path
                    from dag join cte
                    where dag.successor = cte.vertex
            )
        select
            length, path
            from cte
            where vertex = 'alpha'
        -- 3, 'alpha→bravo→charlie→delta'
        -- 3, 'alpha→bravo→foxtrot'
        -- 3, 'alpha→echo→foxtrot'
        -- 4, 'alpha→golf→hotel'
    )
),
cte as (
    select
            length,
            path,
            -1 as "index",
            '' as vertex,
            path||'→' as rest
        from intermediate
    union all
    select
            length,
            path,
            "index" + 1,
            substr(rest, 0, instr(rest, '→')),
            substr(rest, instr(rest, '→') + 1)
        from cte
        where rest != ''
)
select length, path, "index", vertex from cte
where vertex != ''
order by path, "index";

我认为这样做是浪费。有没有可能没有字符串连接和拆分，没有中间结果集？
目标平台是模糊的现代sql。上面的代码已经过测试，并用sqlite运行。