单表一对多关系查询

[tkdg0224]

表中用一个PID字段记录同一张表中的ID,现希望在表中查询父子记录的数据


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,Context,(select Context from #tmp where pid=id )as pContext from #tmp

DROP TABLE #tmp

[發糞塗牆]

你想得到这样？

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

*/

[exception92]

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 B

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

[tkdg0224]

这时想要的/* 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