現在有A,B兩個表.結構如下
A表
ID Name
1 AAA
2 BBB
3 CCC
4 DDD
5 EEE
B表
ID PID
1
2 1
3 2
4 3
5 1
B表的PId字段代表ID的父節點.
得到結果得到所有的Id,及它們的name和父節點的name;結果如下:
ID Name PName
1 AAA
2 BBB AAA
3 CCC BBB
4 DDD CCC
5 EEE AAA
A表
ID Name
1 AAA
2 BBB
3 CCC
4 DDD
5 EEE
B表
ID PID
1
2 1
3 2
4 3
5 1
B表的PId字段代表ID的父節點.
得到結果得到所有的Id,及它們的name和父節點的name;結果如下:
ID Name PName
1 AAA
2 BBB AAA
3 CCC BBB
4 DDD CCC
5 EEE AAA
select b.id,a.name,c.name
from tb b
left join ta a
on a.id=b.id
left join ta c
on a.id=b.pid
left join
(select b.id,a.name from a,b where b.pid = a.id ) t
on a.id = t.id
應該是select b.id,a.name,c.name
from B b
left join A a
on a.id=b.id
left join A c
on c.id=b.pid
insert into a values(1, 'AAA')
insert into a values(2, 'BBB')
insert into a values(3, 'CCC')
insert into a values(4, 'DDD')
insert into a values(5, 'EEE')
create table b(ID int,PID int)
insert into b values(1, null)
insert into b values(2, 1 )
insert into b values(3, 2 )
insert into b values(4, 3 )
insert into b values(5, 1 )
goselect a.* , isnull(t.name,'') pname from a
left join
(select b.id,a.name from a,b where b.pid = a.id ) t
on a.id = t.iddrop table a,b/*
ID Name pname
----------- ---------- ----------
1 AAA
2 BBB AAA
3 CCC BBB
4 DDD CCC
5 EEE AAA(所影响的行数为 5 行)
*/