有一数据表AAA,字段HeTongID是唯一的,若某数据行的字段 ZhuHeongID 不为空(ZhuHeongID的值是从HeTongID中选定出来的),则该行是补充合同。如何将补充合同的主合同的结算状态(Img字段)选出来???
表AAA
HeTongID HeTongBianHao ZhuHeTongID Img
1 A AA
2 B BB
3 C 1
4 D 2 DD如何实现 筛选 HeTongID 为 3 时(HeTongID 为 3 的主合同ID 为 1 ),得到如下数据
HeTongBianHao=C Img=AA ; 筛选 HeTongID 为 4 时(HeTongID 为 4 的主合同ID 为 2 ),得到如下数据
HeTongBianHao=D Img=BB 的语句如何写???
表AAA
HeTongID HeTongBianHao ZhuHeTongID Img
1 A AA
2 B BB
3 C 1
4 D 2 DD如何实现 筛选 HeTongID 为 3 时(HeTongID 为 3 的主合同ID 为 1 ),得到如下数据
HeTongBianHao=C Img=AA ; 筛选 HeTongID 为 4 时(HeTongID 为 4 的主合同ID 为 2 ),得到如下数据
HeTongBianHao=D Img=BB 的语句如何写???
from aaa a,aaa b
where a.hetongid=b.zhuhetongid
and a.hetongid=3
from aaa a,aaa b
where a.zhuhetongid=b.hetongid
and a.hetongid=3
insert @t select 1 , 'A' , null , 'AA'
insert @t select 2 , 'B' , null , 'BB'
insert @t select 3 , 'C' , 1 , null
insert @t select 4 , 'D' , 2 , 'DD'select a.hetongbianhao,b.img
from @t a,@t b
where a.zhuhetongid=b.hetongid
and a.hetongid=3/*
hetongbianhao img
------------- ----------
C AA
*/
create table #tb
(
HeTongID int,
HeTongBianHao char(2),
ZhuHeTongID int,
img char(5)
)insert #tb select 1 , 'A' , null , 'AA'
insert #tb select 2 , 'B' , null , 'BB'
insert #tb select 3 , 'C' , 1 , null
insert #tb select 4 , 'D' , 2 , 'DD'goselect aa.HeTongBianHao,bb.img from #tb as aa
inner join #tb as bb on aa.ZhuHeTongID = bb.HeTongID
where aa.HeTongID = 3 and aa.ZhuHeTongID = 1
from @t a,@t b
where a.zhuhetongid=b.hetongid
and a.hetongid=4/*
hetongbianhao img
------------- ----------
D BB
*/