select inouttime, personno, personname, departmentname,DEPARTMENTCODE, inoutflag
    from SC_INOUT_VALID_VIEW t1
        where t1.inouttime=(select max(inouttime) from SC_INOUT_VALID_VIEW t2 
            where t1.personno=t2.personno and trunc(t2.inouttime)=to_date('2012-09-14','yyyy-mm-dd')
                and substr(DEPARTMENTCODE,1,2) = '12')--如果每個部門persor唯一,此處可省
            and inoutflag='进' and substr(DEPARTMENTCODE,1,2) = '12';

------解决方案--------------------

SQL code

SELECT INOUTTIME,
       PERSONNO,
       PERSONNAME,
       DEPARTMENTNAME,
       DEPARTMENTCODE,
       INOUTFLAG
  FROM (SELECT PERSONNO,
               PERSONNAME,
               INOUTTIME,
               DEPARTMENTNAME,
               DEPARTMENTCODE,
               INOUTFLAG,
               ROW_NUMBER() OVER(PARTITION BY SUBSTR(DEPARTMENTCODE, 1, 2) ORDER BY INOUTTIME DESC) ROW_
          FROM SC_INOUT_VALID_VIEW
         WHERE INOUTTIME >= TO_DATE('2012-09-14', 'YYYY-MM-DD HH24:MI:SS')
           AND INOUTTIME <= TO_DATE('2012-09-15', 'YYYY-MM-DD HH24:MI:SS')
           AND INOUTFLAG = '进')
 WHERE ROW_ = 1;