如何将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)

Fiddle

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))

Fiddle