如何将SQL查询从MySQL 8转换为MySQL 5.7

Question

如何将此代码修复为MySQL 5.7？

with recursive u as
(select t.id, t.refferal, t.balance from users t where refferal = 1
union
select t.id, t.refferal, t.balance from u inner join users t
on u.id = t.refferal)
(select u.id,u.refferal,u.balance from u)

小提琴

Answer 1

受到这回答的启发：

select  id,
        refferal,
        balance
from    (select * from users
         order by refferal, id) u,
        (select @pv := 1) v
where   find_in_set(refferal, @pv)
and     length(@pv := concat(@pv, ',', id))

小提琴