mysql sql语句变量问题

Question

SQL语句如下

SELECT ( @c:=s.id) as 'iid',
 (
    select f._id  from (SELECT @c as '_id',(select @c :=parent_id from sys_menu where id = @c) as 'pt'
     from sys_menu sy) f  where f.pt=0
 ) as 'parent'
 from   (select *  from sys_menu where id in(7,8,11)) s;
**
---结果中parent 应该为 1，2，3 ，返回的是1，1，1，不知道问题在哪里**


 #子语句运行结果
 set @c:=5;
 select f._id  from (SELECT @c as '_id',(select @c :=parent_id from sys_menu where id = @c) as 'pt'
    from sys_menu sy) f  where f.pt=0  
 ----结果为1

数据库中的数据如下

id parent_id
1 0
2 0
3 0
5 1
6 1
7 1
8 2
9 2
10 2
11 3
12 3
13 3
14 13
15 13
16 13

Answer 1

要显示子父级，你直接查表就可以显示，如果要显示递归的层级，那你直接用 with recursive temp就能查所有的啊

with recursive temp as (
select * from sys_menu p  
union all 
 select t.* from sys_menu t inner join temp t2 on t2.id = t.parent_id 
)
select *  from temp 

with recursive temp as (
select * from sys_menu p where p.id in (7,8,11) 
union all 
 select t.* from sys_menu t inner join temp t2 on t2.id = t.parent_id 
)
select *  from temp