SQL> select co,pmdp,mpid from t;

CO           PMDP                     MPID
------------ ------------------------ ------------------------------------
6            2111                     C*

SQL> SELECT co,orgnlvdp,mpid,pmcen FROM txd000paa41 WHERE co='6' AND mpid='C*';

CO           ORGNLVDP                 MPID
------------ ------------------------ ------------------------------------
PMCEN
------------------------
6            21                       C*
21C*

6            2113                     C*
11C**


SQL> SELECT 
  2   (SELECT PMCEN FROM 
  3    (
  4      SELECT PMCEN FROM txd000paa41 WHERE CO=T.CO AND MPID=T.MPID  
  5        AND ORGNLVDP= CASE 
  6        WHEN ORGNLVDP=T.PMDP THEN T.PMDP 
  7        WHEN ORGNLVDP=SubStr(T.PMDP,1,3) THEN SubStr(T.PMDP,1,3) 
  8        WHEN ORGNLVDP=SubStr(T.PMDP,1,2) THEN SubStr(T.PMDP,1,2) 
  9        WHEN ORGNLVDP=SubStr(T.PMDP,1,1) THEN SubStr(T.PMDP,1,1) 
 10        ELSE NULL END 
 11      ORDER BY Length(ORGNLVDP) DESC
 12    ) WHERE ROWNUM=1
 13   )PMCEN
 14  FROM T;

PMCEN
------------------------
11C**

SQL> SELECT 
  2    (SELECT DISTINCT Last_Value(PMCEN) over (ORDER BY Length(ORGNLVDP) ROWS  BETWEEN unbounded pr
eceding AND unbounded following)
  3     FROM txd000paa41 WHERE CO=T.CO AND MPID=T.MPID AND ORGNLVDP= CASE WHEN ORGNLVDP=T.PMDP THEN 
T.PMDP 
  4                            WHEN ORGNLVDP=SubStr(T.PMDP,1,3) THEN SubStr(T.PMDP,1,3) 
  5                            WHEN ORGNLVDP=SubStr(T.PMDP,1,2) THEN SubStr(T.PMDP,1,2) 
  6                            WHEN ORGNLVDP=SubStr(T.PMDP,1,1) THEN SubStr(T.PMDP,1,1) 
  7                            ELSE NULL END
  8    )pmcen 
  9  FROM T;

PMCEN
------------------------
21C*
 


--===============补充执行计划如下=================
SQL> explain plan FOR 
  2  SELECT 
  3   (SELECT PMCEN FROM 
  4    (
  5      SELECT PMCEN FROM txd000paa41 WHERE CO=T.CO AND MPID=T.MPID  
  6        AND ORGNLVDP= CASE 
  7        WHEN ORGNLVDP=T.PMDP THEN T.PMDP 
  8        WHEN ORGNLVDP=SubStr(T.PMDP,1,3) THEN SubStr(T.PMDP,1,3) 
  9        WHEN ORGNLVDP=SubStr(T.PMDP,1,2) THEN SubStr(T.PMDP,1,2) 
 10        WHEN ORGNLVDP=SubStr(T.PMDP,1,1) THEN SubStr(T.PMDP,1,1) 
 11        ELSE NULL END 
 12      ORDER BY Length(ORGNLVDP) DESC
 13    ) WHERE ROWNUM=1
 14   )PMCEN
 15  FROM T;

已做解釋.

SQL> 
SQL> select * from table(DBMS_XPLAN.Display);

PLAN_TABLE_OUTPUT
--------------------------------------------
Plan hash value: 416088130

--------------------------------------------
-------

| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Tim
e     |

--------------------------------------------
-------


PLAN_TABLE_OUTPUT
--------------------------------------------
|   0 | SELECT STATEMENT        |             |     1 |    10 |     3   (0)| 00:
00:01 |

|*  1 |  COUNT STOPKEY          |             |       |       |            |
      |

|   2 |   VIEW                  |             |    15 |   120 |     4  (25)| 00:
00:01 |

|*  3 |    SORT ORDER BY STOPKEY|             |    15 |   225 |     4  (25)|