SELECT T3.* FROM( SELECT @ids as _ids, ( SELECT @ids := GROUP_CONCAT(id) FROM cloud_category WHERE FIND_IN_SET(parent_id, @ids) ) as T1, @l := @l+1 as level_ FROM cloud_category, (SELECT @ids := 100000007000000, @l := 0 ) T4 WHERE @ids IS NOT NULL ) T2, cloud_category T3 WHERE FIND_IN_SET(T3.id, T2._ids) and is_deleted = 0 and STATUS = 1 ORDER BY T3.parent_id, T3.position ASC
mysql 8
with recursive T as ( select * from cloud_category where id = 100000007000000 and is_deleted = 0 union all select C.* from cloud_category C inner join T on C.parent_id = T.id where C.is_deleted = 0 ) select * from T