SELECT AA.ITMREF_0,AA.ITMDES1_0,AA.ITMSTD_0,AA.ZPS,AA.STU_0,ROUND(AA.ZPS/AA.PUUSTUCOE_0) AS ZXS,AA.PUU_0, TO_NUMBER(REPLACE(BB.TEXTE_0,'ml','')) AS HSS,(AA.ZPS/2000*TO_NUMBER(REPLACE(BB.TEXTE_0,'ml',''))/500) AS ZDS FROM (SELECT A.ITMREF_0,B.ITMDES1_0,B.ITMSTD_0,A.ZPS,B.STU_0,ROUND(A.ZPS/B.PUUSTUCOE_0) AS ZXS,B.PUU_0,B.PUUSTUCOE_0,B.TSICOD_3 FROM (SELECT ITMREF_0,SUM(CPLQTY_0) AS ZPS FROM MFGITMTRK WHERE STA_0='A1' AND ITMREF_0 LIKE '1%' AND MFGFCY_0='S01' GROUP BY ITMREF_0)A INNER JOIN ITMMASTER B ON A.ITMREF_0=B.ITMREF_0)AA INNER JOIN ATEXTRA BB ON AA.TSICOD_3=BB.IDENT2_0 WHERE BB.CODFIC_0='ATABDIV' AND BB.ZONE_0='LNGDES' AND BB.LANGUE_0='CHI' AND BB.IDENT1_0='23' ORDER BY AA.TSICOD_3 注:这个语句重点在于TO_NUMBER(REPLACE(BB.TEXTE_0,'ml','')) 这个函数是应用,因为它可以达到将500ml、50ml、1000ml、1ml等这些数变成500、50、1000、1数值,特别是在做产品统计组的时候可以用到。 |