主料往下展开
SELECT bmb01,bmb02,bmb03,bmb06,ima18,numnn
FROM
(
SELECT bmb01,bmb02,bmb03,(bmb06/NVL(bmb07,1)) bmb06,LEVEL as numnn
FROM bmb_file t
START WITH t.bmb01 = ‘VHT-HT74012’
AND (bmb04 <=SYSDATE OR bmb04 IS NULL)
AND (bmb05 > SYSDATE OR bmb05 IS NULL)
CONNECT BY PRIOR bmb03 = bmb01 AND (bmb04 <=SYSDATE OR bmb04 IS NULL) AND (bmb05 > SYSDATE OR bmb05 IS NULL)
ORDER BY LEVEL,bmb01
) a ,ima_file c
WHERE a.bmb03 = c.ima01
原料往上展
SELECT bmb01,bmb02,bmb03,bmb06,ima18,numnn
FROM
(SELECT bmb01,bmb02,bmb03,(bmb06/NVL(bmb07,1)) bmb06,LEVEL as numnn
FROM bmb_file t
START WITH t.bmb03 = ‘BLX5C99A46-54O’
AND (bmb04 <=SYSDATE OR bmb04 IS NULL)
AND (bmb05 > SYSDATE OR bmb05 IS NULL)
CONNECT BY PRIOR bmb01=bmb03 AND (bmb04 <=SYSDATE OR bmb04 IS NULL) AND (bmb05 > SYSDATE OR bmb05 IS NULL)
ORDER BY LEVEL,bmb01
) a ,ima_file c
WHERE a.bmb03 = c.ima01
另一种写法。
select ‘第 ‘||a.L||’ 层’ 层级,(case (L)
when 0 then ‘0’
when 1 then ‘.1’
when 2 then ‘..2’
when 3 then ‘…3’
when 4 then ‘….4’
when 5 then ‘…..5’
when 6 then ‘……6’
when 7 then ‘…….7′
end) Lev,
a.bmb03 料号,ima02 品名,ima021 规格,a.bmb06 组成用量,a.bmb07 底数,
a.bmb08 损耗率,a.bmb10 发料单位,ima08 来源码,ima18 产品单重
from (
select 0 L,bma01 bmb03,0 bmb06,0 bmb07,0 bmb08,” bmb10
from bma_file
where bma01=’0055-JG01-000J’
UNION
select level L,bmb03,bmb06,bmb07,bmb08,bmb10
from bmb_file
where bmb05 is null
start with bmb01= ‘0055-JG01-000J’
connect by prior bmb03=bmb01) a,ima_file
where a.bmb03=ima01
想要固定一列用来显示最顶层料号
SELECT LAG(bma01 ignore nulls,1,bma01) OVER (ORDER BY numnn) AS topitem,bmb01,bmb02,bmb03,ima02,ima021,ima25,bmb06,ima18,numnn FROM (
SELECT (SELECT MAX(bma01) FROM bma_file WHERE bma01=bmb01 AND numnn=1) bma01,bmb01,bmb02,bmb03,ima02,ima021,ima25,bmb06,ima18,numnn
FROM (
SELECT bmb01,bmb02,bmb03,(bmb06/NVL(bmb07,1)) bmb06,LEVEL as numnn
FROM bmb_file t
START WITH t.bmb01 =’1CCWF4497B00000′
AND (bmb04 <=SYSDATE OR bmb04 IS NULL)
AND (bmb05 > SYSDATE OR bmb05 IS NULL)
CONNECT BY PRIOR bmb03 = bmb01 AND (bmb04 <=SYSDATE OR bmb04 IS NULL) AND (bmb05 > SYSDATE OR bmb05 IS NULL)
ORDER BY LEVEL,bmb01
) a ,ima_file c
WHERE a.bmb03 = c.ima01)
转载请注明：赫非域 » TIPTOP BOM展开尾阶SQL，阶层展开，层次展开