各位兄弟，以下是我遇到的问题
传统order by后用rownum=1抓数据，和用分析函数Last_Value()抓数据，发现二者结果不一致。为嘛呢？..
Last_Value()抓的数据 21C* 是对的，order by之后感觉自动忽视了部分where条件,感觉抓到SELECT PMCEN FROM txd000paa41 WHERE CO=T.CO AND MPID=T.MPID里order by的第一个值。
PS：传统order by的方式最内层的子查询，在部分版本的ORACLE可能识别不到T.PMDP。
我的版本可以..
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
- SQL code
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 preceding 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)| Time |---------------------------------------------------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)| 00:00:01 |PLAN_TABLE_OUTPUT--------------------------------------------| 4 | TABLE ACCESS FULL | TXD000PAA41 | 15 | 225 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | T | 1 | 10 | 3 (0)| 00:00:01 |---------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM=1) 3 - filter(ROWNUM=1)已選取 18 個資料列.SQL> explain plan FOR 2 SELECT 3 (SELECT DISTINCT Last_Value(PMCEN) over (ORDER BY Length(ORGNLVDP) ROWS BETWEEN unbounded preceding AND unbounded following) 4 FROM txd000paa41 WHERE CO=T.CO AND MPID=T.MPID AND ORGNLVDP= CASE WHEN ORGNLVDP=T.PMDP THEN T.PMDP 5 WHEN ORGNLVDP=SubStr(T.PMDP,1,3) THEN SubStr(T.PMDP,1,3) 6 WHEN ORGNLVDP=SubStr(T.PMDP,1,2) THEN SubStr(T.PMDP,1,2) 7 WHEN ORGNLVDP=SubStr(T.PMDP,1,1) THEN SubStr(T.PMDP,1,1) 8 ELSE NULL END 9 )pmcen 10 FROM T;已做解釋.SQL> SQL> SQL> select * from table(DBMS_XPLAN.Display);PLAN_TABLE_OUTPUT--------------------------------------------Plan hash value: 1796841893------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 || 1 | HASH UNIQUE | | 1 | 15 | 4(50)| 00:00:01 || 2 | WINDOW SORT | | 1 | 15 | 4(50)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| TXD000PAA41 | 1 | 15 | 2 (0)| 00:00:01 |PLAN_TABLE_OUTPUT--------------------------------------------|* 4 | INDEX RANGE SCAN | PK_TXD000PAA41 | 1 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | T | 1 | 10 | 3 (0)| 00:00:01 |------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("CO"=:B1 AND "MPID"=:B2) filter("MPID"=:B1 AND "ORGNLVDP"=CASE "ORGNLVDP" WHEN :B2 THEN :B3 WHEN SUBSTR(:B4,1,3) THEN SUBSTR(:B5,1,3) WHEN SUBSTR(:B6,1,2) THEN SUBSTR(:B7,1,2) WHEN SUBSTR(:B8,1,1) THEN SUBSTR(:B9,1,1) ELSE NULL END )已選取 20 個資料列.