起因
这次处理的问题,特定性比较强,可能不能拿去直接用,需要结合自己的需求去调整。
今天开单员让我帮忙按指定条件导出一些物料,只要物料描述里包含某些指定字符的就行,之后又说要导出一些对应的特性值,随即就写了下面这段代码
经过
select distinct makt.matnr "物料号", makt.maktx "物料描述", ifnull(njcc.atwrt, '') "内径尺寸", ifnull(wjcc.atwrt, '') "外径尺寸" from makt left outer join MAPL on makt.matnr = mapl.matnr and makt.mandt = mapl.mandt left outer join inob on --inob.objek = concat('N', mapl.PLNNR, MAPL.PLNAL) and inob.objek = concat(concat('N', mapl.PLNNR), MAPL.PLNAL) and inob.mandt = mapl.mandt left outer join (select ausp.mandt, ausp.objek, ausp.atwrt from ausp inner join cabn on ausp.atinn = cabn.atinn and ausp.mandt = cabn.mandt inner join CABNt on cabn.mandt = cabnt.mandt and cabn.atinn = cabnt.atinn where cabn.mandt = '800' and cabn.atnam like 'Z%' and cabnt.spras = '1' and cabnt.ATBEZ in ('内径尺寸')) as njcc on njcc.mandt = inob.mandt and njcc.objek = inob.cuobj left outer join (select ausp.mandt, ausp.objek, ausp.atwrt from ausp inner join cabn on ausp.atinn = cabn.atinn and ausp.mandt = cabn.mandt inner join CABNt on cabn.mandt = cabnt.mandt and cabn.atinn = cabnt.atinn where cabn.mandt = '800' and cabn.atnam like 'Z%' and cabnt.spras = '1' and cabnt.ATBEZ in ('外径尺寸')) as wjcc on wjcc.mandt = inob.mandt and wjcc.objek = inob.cuobj where makt.mandt = '800' and (makt.maktx like '%英联%' --or makt.maktx like '%酵母%' ) and makt.matnr like '00000000001%' order by makt.matnr; |
结果
记录下来的原因是因为这个查询关联的表有点儿多,从物料主数据,到对应的工艺路线,到工艺路线抬头的特性值。