Use RANK() OVER kick out the lower bo_engineer_id, get the toppest.

 
  
  1. CREATE OR REPLACE VIEW V_ENG_PROCCESS AS 
  2. SELECT *  
  3.   FROM (SELECT C.*,  
  4.                RANK() OVER(PARTITION BY C.BO_ENGINEERING_ID ORDER BY C.PROJECT_STATE DESC) LEV  
  5.           FROM (SELECT DISTINCT X.BO_ENGINEERING_ID, X.PROJECT_STATE  
  6.                   FROM (SELECT T1.*,  
  7.                                DECODE(T4.TASKID,  
  8.                                       NULL,  
  9.                                       DECODE(T3.TASKID,  
  10.                                              NULL,  
  11.                                              DECODE(T2.TASKID,  
  12.                                                     NULL,  
  13.                                                     DECODE(T5.TASKID,  
  14.                                                            NULL,  
  15.                                                            '',  
  16.                                                            '5'),  
  17.                                                     '6'),  
  18.                                              '7'),  
  19.                                       '8') PROJECT_STATE  
  20.                           
  21.                           FROM BO_ENG_TASK T1,  
  22.                                (SELECT T.*  
  23.                                   FROM BO_PHASE_TASK T  
  24.                                  WHERE T.TASKCODE = 'CBYSSQ' 
  25.                                    AND T.PHASE_TASK_STATE = '3') T2,  
  26.                                (SELECT T.*  
  27.                                   FROM BO_PHASE_TASK T  
  28.                                  WHERE T.TASKCODE = 'GCCBYSHYJY' 
  29.                                    AND T.PHASE_TASK_STATE = '3') T3,  
  30.                                (SELECT T.*  
  31.                                   FROM BO_PHASE_TASK T  
  32.                                  WHERE T.TASKCODE = 'GCJGPF' 
  33.                                    AND T.PHASE_TASK_STATE = '3') T4,  
  34.                                (SELECT T.*  
  35.                                   FROM BO_PHASE_TASK T  
  36.                                  WHERE T.TASKCODE = 'CBYSSQ' 
  37.                                    AND T.PHASE_TASK_STATE != '3') T5  
  38.                          WHERE T1.TASKID = T2.TASKID(+)  
  39.                            AND T1.TASKID = T3.TASKID(+)  
  40.                            AND T1.TASKID = T4.TASKID(+)  
  41.                            AND T1.TASKID = T5.TASKID(+)  
  42.                          ORDER BY PROJECT_STATE DESC) X  
  43.                  WHERE PROJECT_STATE IS NOT NULL) C) XX  
  44.  WHERE XX.LEV = 1  
  45. /**  
  46.  * 在建工程列表、工程台账中‘工程阶段’的数据源  
  47.  * 阶段代码：在建 5 待初验 6 待竣工 7  已竣工 8  
  48.  * @Add by Zhou Danyong 2010-11-25  
  49.  */;