SELECT CASE status WHEN 0 THEN '待取料' ELSE NULL END AS status,UPDATE_TIME,LIFEX,MATNR, MENGE,INNER_VENDOR_NAME,SUM(status) as number_row FROM (SELECT a.UPDATE_TIME,a.LIFEX,a.MATNR, a.MENGE,d.INNER_VENDOR_NAME,NVL(c.STATUS,0) AS status, ROW_NUMBER() OVER(PARTITION BY a.LIFEX ORDER BY a.LIFEX) RN FROM WMS.PO_IDN_INFO a LEFT JOIN WMS.POERP_OUTERPACKAGE c ON a.LIFEX = c.DELIVERY_NO LEFT JOIN WMS.WMS_NAME d ON a.LIFNR=d.LIFNR WHERE a.UPDATE_TIME between to_date(to_char(sysdate-7,'yyyy/mm/dd'),'yyyy/mm/dd') and to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd') AND c.status = 0 ) WHERE rn=1 GROUP BY UPDATE_TIME,LIFEX,MATNR,MENGE,INNER_VENDOR_NAME,status ORDER BY UPDATE_TIME DESC,LIFEX,MATNR,MENGE,INNER_VENDOR_NAME,status |