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
*/
[解决办法]
等大版或者小三来解决。
[解决办法]
DECLARE @tableA TABLE(AID VARCHAR(50),[TYPE] INT);DECLARE @tableB TABLE(BID VARCHAR(50),ShangJiB VARCHAR(50),Number INT);INSERT INTO @tableASELECT '000',1 UNION ALLSELECT 'AAA001',1 UNION ALLSELECT 'AAABBB001',1 UNION ALLSELECT 'XXXXX001',2 UNION ALLSELECT 'XXXXXYYYYY001',1 INSERT INTO @tableBSELECT '000','000',0 UNION ALL --'000' 的上级不能为自身SELECT 'AAA001','000',1 UNION ALLSELECT 'AAABBB001','AAA001',2 UNION ALLSELECT 'XXXXX001','AAABBB001',3 UNION ALLSELECT '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.shangjibunion allselect 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.aidinner join cte c on a.shangjib=c.bidwhere a.bid<>a.shangjib)select * from cte/*BID ShangJiB Number type-------------------------------------------------- -------------------------------------------------- ----------- -----------000 0 1AAA001 000 1 1AAABBB001 AAA001 2 1XXXXX001 AAABBB001 3 2XXXXXYYYYY001 XXXXX001 4 2(5 行受影响)*/
