SQL SHOW系列（五）

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

CREATE OR REPLACE VIEW V_ENG_PROCCESS AS 

SELECT *  

  FROM (SELECT C.*,  

               RANK() OVER(PARTITION BY C.BO_ENGINEERING_ID ORDER BY C.PROJECT_STATE DESC) LEV  

          FROM (SELECT DISTINCT X.BO_ENGINEERING_ID, X.PROJECT_STATE  

                  FROM (SELECT T1.*,  

                               DECODE(T4.TASKID,  

                                      NULL,  

                                      DECODE(T3.TASKID,  

                                             NULL,  

                                             DECODE(T2.TASKID,  

                                                    NULL,  

                                                    DECODE(T5.TASKID,  

                                                           NULL,  

                                                           '',  

                                                           '5'),  

                                                    '6'),  

                                             '7'),  

                                      '8') PROJECT_STATE  

                          FROM BO_ENG_TASK T1,  

                               (SELECT T.*  

                                  FROM BO_PHASE_TASK T  

                                 WHERE T.TASKCODE = 'CBYSSQ' 

                                   AND T.PHASE_TASK_STATE = '3') T2,  

                                 WHERE T.TASKCODE = 'GCCBYSHYJY' 

                                   AND T.PHASE_TASK_STATE = '3') T3,  

                                 WHERE T.TASKCODE = 'GCJGPF' 

                                   AND T.PHASE_TASK_STATE = '3') T4,  

                                   AND T.PHASE_TASK_STATE != '3') T5  

                         WHERE T1.TASKID = T2.TASKID(+)  

                           AND T1.TASKID = T3.TASKID(+)  

                           AND T1.TASKID = T4.TASKID(+)  

                           AND T1.TASKID = T5.TASKID(+)  

                         ORDER BY PROJECT_STATE DESC) X  

                 WHERE PROJECT_STATE IS NOT NULL) C) XX  

 WHERE XX.LEV = 1  

/**  

 * 在建工程清單、工程台賬中‘工程階段’的資料源  

 * 階段代碼：在建 5 待初驗 6 待竣工 7  已竣工 8  

 * @Add by Zhou Danyong 2010-11-25  

 */;  

     本文轉自danni505 51CTO部落格，原文連結：http://blog.51cto.com/danni505/432295，如需轉載請自行聯系原作者