-- original_batch_no(父节点) batch_no(子节点)
select t2.id, t2.batch_no, t2.original_batch_no
from (SELECT @r as _batch_no,
(Select @r := original_batch_no from e_mes_pd_batch where batch_no = _batch_no) as original_batch_no,
@l := @l + 1 as lvl
-- 查询子节点(G220608001AA2) 对应 根节点
from (select @r := 'test06081434', @l := 0) vars,
e_mes_pd_batch h
where @r is not null) t1
left join e_mes_pd_batch t2 on t1._batch_no = t2.batch_no
where t2.original_batch_no is null;