SQL递归求助？

----@tableA表,@tableB表
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
SELECT 'AAA001','000',1 UNION ALL
SELECT 'AAABBB001','AAA001',2 UNION ALL
SELECT 'XXXXX001','AAABBB001',3 UNION ALL
SELECT 'XXXXXYYYYY001','XXXXX001',4

SELECT * FROM @tableB

--结果是 :BID表示的是编号，ShangJiB上级编号，Number 级别，如果BID的上级编号有一个是Type为2的，就返回2，否则就返回1
/*编号 类型
 000 1
 AAA001 1
 AAABBB001 1
 XXXXX001 2  
 XXXXXYYYYY001 2
 */

等大版或者小三来解决。

SQL code

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','',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.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
)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 行受影响)

*/

哈哈.我抢位了.....

select BID,
  类别=case when exists(select 1 from A where AID=t.ShangJiB and type=2) then 2 else 1 end
from B t

你解决不是一样么？也是大大级别的

谢谢楼上各位的解答！！

不修改 000可否实现吗?

程序要改一下,否则是死循环.

SQL code

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 行受影响)

*/

谢谢晴天大哥