how can mysql5 do the same thing just as oracle connect by prior order siblings by a field?

Question

there are many ways to do the connect by prior with mysql5, (I have known mysql8 support this query,however, I'm using mysql5), I had known that I can use find_in_set to realize the connect by prior, but how can I realize order siblings by just as oracle did. for example, here is the test data e,my real data is that, the id is guid just like

62CF6FFEDE064B00ADFF43ED0E6EFDDB

create table test(id varchar(10),ordernumber decimal,pid varchar(10));
insert into test values('62CF6',2,'');
insert into test values('44DF8',3,'D13A3');
insert into test values('DD43F',5,'62CF6');
insert into test values('E7832',2,'53FAA');
insert into test values('48728',4,'D13A3');
insert into test values('53FAA',4,'62CF6');
insert into test values('9038A',1,'53FAA');
insert into test values('F5C7E',2,'D13A3');
insert into test values('D13A3',1,'');

in oracle i can use

starts with pid is null connect by prior id=pid order siblings by ordernumber asd

to get this result

id     ordernumber  pid
D13A3     1    
F5C7E     2         D13A3
44DF8     3         D13A3
48728     4         D13A3
62CF6     2
53FAA     4         62CF6
9038A     1         53FAA
E7832     2         53FAA
DD43F     5         62CF6

since the level is unknown,I can only use the procedure like just example code not real runnable.

create procedure test_p(in p varchar(100))
begin
declare aa varchar;
declare unit_cur cursor for select id from table where pid=p;
open unit_cur
fetch unit_cur into aa
call test_p(aa);

then if I have millions of tables to handle, how can i make only one procedure to the the similar thing?

Answer 1

SELECT *
FROM test
ORDER BY CASE WHEN pid = '' 
              THEN id 
              ELSE pid 
              END, 
         ordernumber;

---

I had changed the data,my true id is guid – JokerSora

SELECT t1.*
FROM test t1
LEFT JOIN test t2 ON t1.pid = t2.id
ORDER BY CASE WHEN t1.pid = ''
              THEN t1.ordernumber 
              ELSE t2.ordernumber 
              END, 
         t1.ordernumber;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e3c01067da653f7ae5ce5c969d0bd57f