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数值，特别是在做产品统计组的时候可以用到。