SQL递归求助？

[liangyong1107]

----@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
*/

[--小F--]

等大版或者小三来解决。

[-晴天]

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

*/

[-晴天]

[Quote=引用 1 楼 fredrickhu 的回复:]

等大版或者小三来解决。
[/Quote]

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

[快溜]

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

[--小F--]

[Quote=引用 3 楼 qianjin036a 的回复:]
引用 1 楼 fredrickhu 的回复:

等大版或者小三来解决。


哈哈.我抢位了.....
[/Quote]

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

[liangyong1107]

谢谢楼上各位的解答！！

[liangyong1107]

[Quote=引用 2 楼 qianjin036a 的回复:]
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',……
[/Quote]
不修改 000可否实现吗?

[-晴天]

[Quote=引用 7 楼 liangyong1107 的回复:]

引用 2 楼 qianjin036a 的回复:
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 U……
[/Quote]
程序要改一下,否则是死循环.

[-晴天]

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

*/

[liangyong1107]

[Quote=引用 9 楼 qianjin036a 的回复:]
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',……
[/Quote]
谢谢晴天大哥