如何通过一条sql语句根据子分类id获取子分类及其所有父分类

数据库：mysql 5.7
表

img


结果

img


怎么通过sql语句获取如图所示的结果

---模拟测试数据
create table test_20220315_c (id int,name VARCHAR(20),pid int);
insert into test_20220315_c values (1,'a',0);
insert into test_20220315_c values (2,'b',1);
insert into test_20220315_c values (3,'c',1);
insert into test_20220315_c values (4,'d',3);

--查询sql
SELECT ID.level, DATA.* FROM( 
SELECT 
@id as _id, 
( SELECT @id := pid 
FROM test_20220315_c 
WHERE id = @id 
) as _pid, 
@l := @l+1 as level 
FROM test_20220315_c, 
(SELECT @id := 4, @l := 0 ) b 
WHERE @id > 0 
) ID, test_20220315_c DATA 
WHERE ID._id = DATA.id 
ORDER BY level;

img

该方法参考自