DECLARE @tableA TABLE(AID VARCHAR(50),[TYPE] INT);
DECLARE @tableB TABLE(BID VARCHAR(50),ShangJiB VARCHAR(50),Number INT);
INSERT INTO @tableA
SELECT '000',1 UNION ALL
SELECT 'AAA001',1 UNION ALL
SELECT 'AAABBB001',1 UNION ALL
SELECT 'XXXXX001',2 UNION ALL
SELECT 'XXXXXYYYYY001',1  

INSERT INTO @tableB
SELECT '000','000',0 UNION ALL   --'000' 的上级不能为自身
SELECT 'AAA001','000',1 UNION ALL
SELECT 'AAABBB001','AAA001',2 UNION ALL
SELECT 'XXXXX001','AAABBB001',3 UNION ALL
SELECT 'XXXXXYYYYY001','XXXXX001',4
;with cte as(
select a.*,(case when b.type=2 then 2 else 1 end)type 
from @tableB a inner join @tableA b on a.bid=b.aid 
where a.bid=a.shangjib
union all
select a.*,(case when b.type=2 or c.type=2 then 2 else 1 end) 
from @tableB a inner join @tableA b on a.bid=b.aid
inner join cte c on a.shangjib=c.bid
where a.bid<>a.shangjib
)select * from cte
/*
BID                                                ShangJiB                                           Number      type
-------------------------------------------------- -------------------------------------------------- ----------- -----------
000                                                                                                   0           1
AAA001                                             000                                                1           1
AAABBB001                                          AAA001                                             2           1
XXXXX001                                           AAABBB001                                          3           2
XXXXXYYYYY001                                      XXXXX001                                           4           2

(5 行受影响)

*/