SQL SHOW系列（五）

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.  */;  

     本文转自danni505 51CTO博客，原文链接：http://blog.51cto.com/danni505/432295，如需转载请自行联系原作者