你想得到这样?create table #tmp(id int,pid varchar(30),Context varchar(30))
insert into #tmp
select 1, null , '1' union all
select 2, null , '2' union all
select 3, null , '3' union all
select 4, null , '4' union all
select 5, 1 , '1_1' union all
select 6, 2 , '2_1' union all
select 6, 3 , '3_1' union all
select 7, 4 , '4_1'
select * from #tmp
--下面这句显然错误
select id,#tmp.Context,b.context as pContext from #tmp LEFT JOIN (select Context,pid from #tmp ) b ON b.pid=id DROP TABLE #tmp/*
id Context pContext
----------- ------------------------------ ------------------------------
1 1 1_1
2 2 2_1
3 3 3_1
4 4 4_1
5 1_1 NULL
6 2_1 NULL
6 3_1 NULL
7 4_1 NULL*/
insert into #tmp
select 1, null , '1' union all
select 2, null , '2' union all
select 3, null , '3' union all
select 4, null , '4' union all
select 5, 1 , '1_1' union all
select 6, 2 , '2_1' union all
select 6, 3 , '3_1' union all
select 7, 4 , '4_1'
select * from #tmp
--下面这句显然错误
select id,#tmp.Context,b.context as pContext from #tmp LEFT JOIN (select Context,pid from #tmp ) b ON b.pid=id DROP TABLE #tmp/*
id Context pContext
----------- ------------------------------ ------------------------------
1 1 1_1
2 2 2_1
3 3 3_1
4 4 4_1
5 1_1 NULL
6 2_1 NULL
6 3_1 NULL
7 4_1 NULL*/
create table #tmp(id int,pid varchar(30),Context varchar(30))
insert into #tmp
select 1, null , '1' union all
select 2, null , '2' union all
select 3, null , '3' union all
select 4, null , '4' union all
select 5, 1 , '1_1' union all
select 6, 2 , '2_1' union all
select 6, 3 , '3_1' union all
select 7, 4 , '4_1'--下面这句显然错误
select B.id,B.Context,(select Context from #tmp as A where A.pid=B.id )as pContext from #tmp as BDROP TABLE #tmpid Context pContext
1 1 1_1
2 2 2_1
3 3 3_1
4 4 4_1
5 1_1 NULL
6 2_1 NULL
6 3_1 NULL
7 4_1 NULL
id Context pContext
----------- ------------------------------ ------------------------------
1 1 NULL
2 2 NULL
3 3 NULL
4 4 NULL
5 1_1 1
6 2_1 2
6 3_1 3
7 4_1 4
*/
谢谢已解决select #tmp.id,#tmp.Context,b.context as pContext
from #tmp LEFT JOIN (select Context,id from #tmp ) b ON b.id= pid