declare @tempTb Table(id nvarchar(64), FGID nvarchar(64),FGshuruma nvarchar(64),status int,wuliaoid nvarchar(64),shuruma nvarchar(64),danweiname nvarchar(64),danhao float)
declare @tempTbs Table(id nvarchar(64), FGID nvarchar(64),FGshuruma nvarchar(64),status int,wuliaoid nvarchar(64),shuruma nvarchar(64),danweiname nvarchar(64),danhao float,type1 nvarchar(10),type2 nvarchar(10))

insert into @tempTb select a.id,a.FGID,a.FGshuruma,a.status,b.wuliaoid,b.shuruma,b.danweiname,b.danhao from 
	(SELECT id,zhiliangdengjiid as FGID,zhiliangdengjiname as FGshuruma,status FROM HJ_bomdanhead as a where a.id=(select top 1 id FROM HJ_bomdanhead as b where b.zhiliangdengjiid=a.zhiliangdengjiid order by status asc)) as a 
	inner join HJ_bomdan2 as b on a.id=b.id where b.danhao is not null and a.FGID not in (SELECT distinct zhiliangdengjiid FROM HJ_peifangbiao where len(zhiliangdengjiid)>0)
	union all
	(select a.id,a.zhiliangdengjiid as FGID,a.shuruma as FGshuruma,a.status,b.wuliaoid,b.shuruma,b.danweiname,b.danhao from (select id,zhiliangdengjiid,shuruma,status from HJ_peifangbiao as a where len(zhiliangdengjiid)>0 and a.id=(select top 1 id from HJ_peifangbiao as b where b.zhiliangdengjiid=a.zhiliangdengjiid order by b.status asc)) as a 
	inner join (select id,wuliaoid,shuruma,danweiname,danhao from HJ_peifangbiao2 where id not in (‘HJWBM150409038‘,‘HJWBM150409040‘)) as b ON a.id=b.id);

insert into @tempTbs select *,case when FGID in (select distinct wuliaoid from @tempTb) then ‘M‘ else ‘FG‘ end as type1,case when wuliaoid in (select distinct FGID from @tempTb) then ‘M‘ else ‘P‘ end as type2 from @tempTb;

with cte(id,FGID,FGshuruma,type1,status,wuliaoid,shuruma,type2,danweiname,danhao,level,up) as
(
select id,FGID,FGshuruma,type1,status,wuliaoid,shuruma,type2,danweiname,danhao,1 as level,FGshuruma as up from @tempTbs
union all
select h.id,h.FGID,h.FGshuruma,h.type1,h.status,c.wuliaoid,c.shuruma,c.type2,c.danweiname,h.danhao*c.danhao/100 as danhao,c.level+1 as level,c.FGshuruma as up from @tempTbs h
inner join cte c on h.wuliaoid=c.FGID
)
select * from cte