一句SQL搞定 BOM展尾阶

*********废话不多说，直接上语句与效果图**********************************

 

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='112636-00000007'
UNION
select level L,bmb03,bmb06,bmb07,bmb08,bmb10
from bmb_file
where bmb05 is null
start with bmb01= '112636-00000007'
connect by prior bmb03=bmb01) a,ima_file
where a.bmb03=ima01

---------执行结果如下图所示-------------------------------------------------------------------