zhouhuan324 于 2013.07.04 17:07 提问

SQL里递归结果如何和其他表进行关联

FROM TF_IJ tf1
LEFT JOIN TF_FL tf2 ON tf2.REM=tf1.REM AND tf2.ITM=tf1.ITM

WHERE tf1.REM!=''AND tf1.PRD_NO LIKE '1010%' AND tf1.REM='MO121001706'AND tf1.PRD_NO= '1010300125'

REM IJ_NO PRD_NO ID_NO qty FL_NO PRD_NO qty
MO121001706 WI130100106 1010300125 1010300125->A0 40 M7130600060 1030100163 300

WITH CTE(bom_no,prd_no,id_no,qty )
AS
(select bom_no,prd_no,id_no,qty from tf_bom WHERE BOM_NO='1010300125->A0'
UNION ALL

SELECT A.BOM_NO,A.PRD_NO,A.id_no,CAST((B.qty*A.QTY) as NUMERIC(28,8)) AS qty
FROM CTE B
INNER JOIN tf_bom A on A.bom_NO=B.id_no )
SELECT bom_no,prd_no,id_no,qty FROM CTE where not exists( select 1 from tf_bom where bom_NO=CTE.id_no)

bom_no prd_no id_no qty
POL111->A0 1020300223 1
POL111->A0 1020300132 1
1010100060-> 1020500024 1

IJ_NO PRD_NO ID_NO qty 1010300125->A0的下层物料 1010300125->A0的最终物料 根据递归的标准用量乘以退料的半成品数 FL_NO PRD_NO qty
WI130100106 1010300125 1010300125->A0 40 POL111->A0 1020300223 1x40 M7130600060 1030100163 300
WI130100106 1010300125 1010300125->A0 40 POL111->A0 1020300132 1x40 M7130600060 1030100163 300
WI130100106 1010300125 1010300125->A0 40 1010100060-> 1020500024 1x40 M7130600060 1030100163 300