您正确选择了递归cte。不幸的是，redshift不支持它们。另见类似问题。我不知道任何sql特性可以被认为是完全值的解决方法。如果级别的数量实际上是有限的，您可以使用有限数量的左自连接（我知道，很难看）。
（对你的cte来说，虽然有点离题，但似乎是身体状况 where edition_no=0 作为联合的第一部分的种子就足够了，第二个查询中的连接条件也应该变成 on f.prev_book_no=e.book_no .)

亚马逊红移数据库不支持递归查询。https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html
我不擅长这种方言。试试下面的代码，也许它能帮你解决问题：

select book_no,prev_book_no,edition_no,
(
select listagg(innr.book_no,',') within group (order by innr.edition_no asc) over() 
from books innr 
where innr.book_no=outr.book_no and innr.edition_no<=outr.edition_no
) tree
from books outr
order by book_no

带循环的变量。尝试执行：

create local temporary table IF NOT EXISTS 
table1 (book_id bigint, book_no varchar(10), edition_no varchar(10));

insert into table1 (book_id, book_no, edition_no) 
select row_number () over (order by book_no) as book_id, book_no, edition_no 
from books where edition_no=0;

FOR iter IN SELECT distinct edition_no 
FROM books where edition_no!=0 ORDER BY edition_no LOOP
  insert into table1 (book_id, book_no, edition_no) 
  select t.book_id, b.book_no, b.edition_no 
  from table1 t join books b on t.book_id=b.prev_book_no
  where b.edition_no = iter
  ;
  commit;
  END LOOP;

select book_no,prev_book_no,edition_no,
(
select listagg(innr.book_no,',') within group (order by innr.edition_no asc) over() 
from books innr inner join table1 tbl_innr on innr.book_no=tbl_innr.book_no
where tbl_innr.book_id=tbl_outr.book_id and innr.edition_no<=outr.edition_no
) tree
from books outr join table1 tbl_outr on outr.book_no=tbl_outr.book_no
order by book_no
;
drop table table1;