select a.name as '班长',b.name as '组长',c.name as '组员'
from (select * from tb where r_id=0)a
left join (select a.* from tb a inner join tb b on a.r_id=b.id where b.r_id=0)b on a.id=b.r_id
left join (
select a.*,b.r_id as new_id from tb a
inner join (select a.* from tb a inner join tb b on a.r_id=b.id where b.r_id=0)b on a.r_id=b.id
)c on a.id=c.new_id
create table pm
(id int,name varchar(10),r_id int,role varchar(10))insert into pm
select 1,'A',0,'班长' union all
select 2,'B',1,'组长' union all
select 3,'C',1,'组长' union all
select 4,'D',2,'组员' union all
select 5,'E',0,'班长' union all
select 6,'F',5,'组长' union all
select 7,'G',6,'组员' union all
select 8,'H',6,'组员' union all
select 9,'I',0,'班长'
select a.name '班长',b.name '组长',c.name '组员'
from (select * from pm where role='班长') a
left join (select * from pm where role='组长') b on a.id=b.r_id
left join (select * from pm where role='组员') c on b.id=c.r_id/*
班长 组长 组员
---------- ---------- ----------
A B D
A C NULL
E F G
E F H
I NULL NULL(5 row(s) affected)
*/
from (select * from cs where r_id=0)a
left join (select a.* from cs a inner join cs b on a.r_id=b.id where b.r_id=0)b on a.id=b.r_id
left join (
select a.*,b.r_id as new_id from cs a
inner join (select b.* from cs b inner join cs c on b.r_id=c.id where c.r_id=0)b on a.r_id=b.id
)c on b.id=c.r_id
drop table cs
create table cs
(id int,name varchar(10),r_id int,role varchar(10))
insert into cs
select 1,'A',0,'班长' union all
select 2,'B',1,'组长' union all
select 3,'C',1,'组长' union all
select 4,'D',2,'组员' union all
select 5,'E',0,'班长' union all
select 6,'F',5,'组长' union all
select 7,'G',6,'组员' union all
select 8,'H',6,'组员' union all
select 9,'I',0,'班长'
select a.name as '班长',b.name as '组长',c.name as '组员'
from (select * from cs where role='班长') a
left join (select * from cs where role='组长') b on a.id=b.r_id
left join (select * from cs where role='组员') c on b.id=c.r_id
from (select t.* from T_test2 t where t.r_id = 0) a
left join (select a1.*
from T_test2 a1
inner join T_test2 b on a1.r_id = b.id
where b.r_id = 0) b on a.id = b.r_id
left join (select a2.*, b.id as new_id
from T_test2 a2
inner join (select a3.*
from T_test2 a3
inner join T_test2 b on a3.r_id = b.id
where b.r_id = 0) b on a2.r_id = b.id) c on b.id =
c.new_id三楼的可行;4楼也可以
select a.name,b.name,c.name,d.name from (select * from a_test where r_id=0) a
left join (select a.*,b.r_id n_id from a_test a,a_test b where a.id=b.id) b on a.id=b.r_id
left join (select a.*,b.r_id n_id from a_test a,a_test b where a.id=b.id) c on b.id=c.r_id
left join (select a.*,b.r_id n_id from a_test a,a_test b where a.id=b.id) d on c.id=d.r_id and d.n_id=c.id
select a.name,b.name,c.name,d.name from (select * from a_test where r_id=0) a
left join (select name,r_id,id from a_test) b on a.id=b.r_id
left join (select name,r_id,id from a_test) c on b.id=c.r_id
left join (select a.*,b.r_id n_id from a_test a,a_test b where a.id=b.id) d on c.id=d.r_id and d.n_id=c.id