单表一对多关系查询,该如何解决
单表一对多关系查询
表中用一个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
------解决方案--------------------
你想得到这样?
------解决方案--------------------
表中用一个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
*/
------解决方案--------------------
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