物料清单BOM简单递归查询原创
41人赞赏了该文章
844次浏览
编辑于2023年06月06日 13:54:59
--BOM物料清单递归查询 WITH t AS( SELECT a.FID, a.FMATERIALID PFMATERIALID, b.FMATERIALID CFMATERIALID, b.FNUMERATOR CFNUMERATOR, b.FDENOMINATOR CFDENOMINATOR, am.FNUMBER PFMATERIALNO, bm.FNUMBER CFMATERIALNO, aml.FNAME PFMATERIALNAME, bml.FNAME CFMATERIALNAME, bml.FSPECIFICATION, u.FNAME CFUNITNAME FROM T_ENG_BOM a INNER JOIN T_ENG_BOMCHILD b ON a.FID = b.FID INNER JOIN T_BD_MATERIAL am ON a.FMATERIALID = am.FMATERIALID INNER JOIN T_BD_MATERIAL_L aml on am.FMATERIALID = aml.FMATERIALID INNER JOIN T_BD_MATERIAL bm ON b.FMATERIALID = bm.FMATERIALID INNER JOIN T_BD_MATERIAL_L bml on b.FMATERIALID = bml.FMATERIALID INNER JOIN T_BD_UNIT_L u on b.FUNITID = u.FUNITID ),t2 AS ( SELECT * FROM t WHERE t.PFMATERIALNO like '%13' UNION ALL SELECT t.* FROM t2 INNER JOIN t on t.PFMATERIALID = t2.CFMATERIALID ) SELECT * FROM t2 OPTION (MAXRECURSION 5)
推荐阅读