递归问题解决方法，求大神帮帮忙！！

Landa_Peter 2013-12-19 01:19:45

Create table aa (ID varchar(20),ItemCode varchar(20),BOMUsage varchar(20))

GO

insert into  aa(ID,ItemCode,BOMUsage)  

select 'S20011','13134313',         '3' union all

select '215131',   'S20011',        '3' union all

select 'Z10211',   'Z10211' ,       '3' union all

select 'Z10311',   'Z10211',        '3' union all

select 'Z10411',   'Z10311' ,       '3' union all

select 'Z10051',   '1313131',       '3' union all

select 'Z10811',   'Z10051',        '3' union all

select 'Z21011',   'Z15411' ,       '3' union all

select 'Z13211',   '1313131' ,      '3' union all

select 'Z10431',   'Z11211',        '3' union all

select 'Z14511',   'Z14511' ,       '3' union all

select 'Z15411',   '1313131',       '3' union all

select 'Z11211',   '1313131',       '3' union all

select 'S20011',   '13134313' ,     '3' union all

select '215131',   'S2001' ,        '3' union all

select 'Z10211',   '1313131' ,      '3' union all

select 'Z10311',   'S20011'  ,      '3' union all

select 'Z10411',   '1313131',       '3' union all

select 'Z10051',   '1313131' ,      '3' union all

select 'Z10811',   '1313131',       '3' union all

select 'Z21011',   'Z10811' ,       '3' union all

select 'Z13211',   '1313131' ,      '3' union all

select 'Z14511',   'Z13211',        '3' union all

select 'Z15411',  'Z10431',         '3' union all

select 'Z11211',   '1313131',       '3'

GO

Create table bb (VerID varchar(20),BOMUsg varchar(20))

GO

insert into bb(VerID,BOMUsg)

select 'S20011','z' union all

select '215131','3' union all

select 'Z10211','z' union all

select 'Z10311','3' union all

select 'Z10411','3' union all

select 'Z10051','3' union all

select 'Z10811','3' union all

select 'Z21011','z' union all

select 'Z13211','3' union all

select 'Z10431','z' union all

select 'Z14511','3' union all

select 'Z15411','3' union all

select 'Z11211','z'

数据如上，想在想用递归方法用aa表的ItemCode去匹配bb表的VerID ，得出的bb表的BOMUsg如果为z的话就把为z的数据取出来放入随便一张表里而匹配出还是为3的话就继续往下递归匹配bb表出现z的话也是把为z的数据取出来放入刚刚那一张表里，为3的话继续递归，中间如果出现BOMUsg为NULL的数据不管，只要继续往下递归，最后能得出BOMUsg都为NULL的数据，我会对BOMUsg为NULL的数据和为z的数据进行处理。求各位大神帮帮忙！

...全文

255 18 打赏收藏转发到动态举报

写回复

用AI写文章

18 条回复

切换为时间正序

请发表友善的回复…

发表回复

Landa_Jimmy 2013-12-19

打赏
举报

WITH cte AS 
(
	SELECT ID,ItemCode,BOMUsage,1 AS leve FROM 
	(
		SELECT aa.ID,aa.ItemCode,aa.BOMUsage FROM aa
		JOIN bb ON aa.ItemCode=bb.VerID AND aa.BOMUsage=bb.BOMUsg
	)a
	UNION ALL
	SELECT b.ID,b.ItemCode,b.BOMUsage,cte.leve+1 AS leve
	FROM 
	(
		SELECT aa.ID,aa.ItemCode,aa.BOMUsage FROM aa
	    JOIN bb ON aa.ItemCode=bb.VerID AND aa.BOMUsage=bb.BOMUsg
	)b JOIN cte ON b.ID=cte.ItemCode 
)
SELECT * FROM cte

雪狐 2013-12-19

打赏
举报

用CTE支持递归查询。下面的语句是对B表的递归，你可以根据你的需要改造一下。 WITH cc AS( SELECT t1.VerID, t1.BOMUsg FROM dbo.bb t1 WHERE t1.BOMUsg = 'z' UNION ALL SELECT t2.VerID, t2.BOMUsg FROM cc AS t1 INNER JOIN dbo.bb AS t2 ON t1.VerID = t2.VerID WHERE t2.BOMUsg = '3' ) SELECT * FROM CC;

發糞塗牆 2013-12-19

打赏
举报

找不到根节点，无法开始递归，除非用游标

Landa_Peter 2013-12-19

打赏
举报

对啊最后只要拆解到bb 表的BOMusg全NULL就行了

Z10411	Z10311	3	NULL	NULL
Z10811	Z10051	3	NULL	NULL
Z21011	Z15411	3	NULL	NULL
Z14511	Z14511	3	NULL	NULL
Z21011	Z10811	3	NULL	NULL
Z14511	Z13211	3	NULL	NULL

这是我要的一部分为3 最后拆到为空就不用拆了要的就是这些数据，还有中间出现bb 表的BOMusg为z的数据

阳泉酒家小当家 2013-12-19

打赏
举报

引用 11 楼 u012158899 的回复:

[quote=引用 10 楼 DBA_Huangzj 的回复:] 那就先把在上面查询中的数据排除再做关联咯，其实如果非要顺序来做，就用游标最好

这是一个树的结构，不好第一步排除的[/quote] 哦，既然是树结构，那么应该有树根把

發糞塗牆 2013-12-19

打赏
举报

但是找不到你的根节点啊

發糞塗牆 2013-12-19

打赏
举报

BOM关系是在AA表实现是吧？

Landa_Peter 2013-12-19

打赏
举报

引用 10 楼 DBA_Huangzj 的回复:

那就先把在上面查询中的数据排除再做关联咯，其实如果非要顺序来做，就用游标最好

这是一个树的结构，不好第一步排除的

發糞塗牆 2013-12-19

打赏
举报

那就先把在上面查询中的数据排除再做关联咯，其实如果非要顺序来做，就用游标最好

Landa_Peter 2013-12-19

打赏
举报

引用 8 楼 DBA_Huangzj 的回复:

SELECT b.* 
FROM aa a LEFT JOIN bb b ON a.itemcode=b.verid
WHERE b.bomusg='z'
UNION 
SELECT b.* 
FROM aa a LEFT JOIN bb b ON a.itemcode=b.verid
WHERE b.bomusg IS NULL

谢谢答复，不过这只能解决一级拆解，后面也会有要继续拆解的，就是bomusg出现为3的情况还要继续关联的

發糞塗牆 2013-12-19

打赏
举报

SELECT b.* 
FROM aa a LEFT JOIN bb b ON a.itemcode=b.verid
WHERE b.bomusg='z'
UNION 
SELECT b.* 
FROM aa a LEFT JOIN bb b ON a.itemcode=b.verid
WHERE b.bomusg IS NULL

Landa_Peter 2013-12-19