传统order by和分析函数结果不一致

Question

各位兄弟，以下是我遇到的问题
传统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> 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)| 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 pr
eceding 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 SUB
STR(:B7,1,2) WHEN              SUBSTR(:B8,1,1) THEN SUBSTR(:B9,1,1) ELSE NULL END )已選取 20 個資料列.

Answer 1

没看全，不过建议如下，你可以试试
(SELECT PMCEN FROM 
  (
    SELECT PMCEN FROM txd000paa41 WHERE CO=T.CO AND MPID=T.MPID  
      AND ORGNLVDP= CASE 
      WHEN ORGNLVDP=T.PMDP THEN T.PMDP 
      WHEN ORGNLVDP=SubStr(T.PMDP,1,3) THEN SubStr(T.PMDP,1,3) 
      WHEN ORGNLVDP=SubStr(T.PMDP,1,2) THEN SubStr(T.PMDP,1,2) 
      WHEN ORGNLVDP=SubStr(T.PMDP,1,1) THEN SubStr(T.PMDP,1,1) 
      ELSE NULL END 
    ORDER BY Length(ORGNLVDP) DESC
  ) WHERE ROWNUM=1)
------------------------这句改成-------------------
(SELECT PMCEN FROM 
  (
    SELECT rownum rw, PMCEN FROM txd000paa41 WHERE CO=T.CO AND MPID=T.MPID  
      AND ORGNLVDP= CASE 
      WHEN ORGNLVDP=T.PMDP THEN T.PMDP 
      WHEN ORGNLVDP=SubStr(T.PMDP,1,3) THEN SubStr(T.PMDP,1,3) 
      WHEN ORGNLVDP=SubStr(T.PMDP,1,2) THEN SubStr(T.PMDP,1,2) 
      WHEN ORGNLVDP=SubStr(T.PMDP,1,1) THEN SubStr(T.PMDP,1,1) 
      ELSE NULL END 
    ORDER BY Length(ORGNLVDP) DESC
  ) WHERE rw=1)

Answer 2

11      ORDER BY Length(ORGNLVDP) DESC这里指定了 desclast_value()那里没有指定desc阿

Answer 3

这样的话，其实order by就相当于没用了吧..

Answer 4

因为应经是用last_value()函数了..不用指定desc了..

Answer 5

俺的oracle还真不认识你的那个order by子查询的SQL，改写了下代码，执行的结果正常。
    SELECT PMCEN FROM 
     (
       SELECT PMCEN 
       FROM txd000paa41,T 
       WHERE txd000paa41.CO=T.CO 
         AND txd000paa41.MPID=T.MPID  
         AND ORGNLVDP= CASE 
         WHEN ORGNLVDP=T.PMDP THEN T.PMDP 
         WHEN ORGNLVDP=SubStr(T.PMDP,1,3) THEN SubStr(T.PMDP,1,3) 
         WHEN ORGNLVDP=SubStr(T.PMDP,1,2) THEN SubStr(T.PMDP,1,2) 
         WHEN ORGNLVDP=SubStr(T.PMDP,1,1) THEN SubStr(T.PMDP,1,1) 
         ELSE NULL END 
       ORDER BY Length(ORGNLVDP) DESC
     ) WHERE ROWNUM=1
结果：
21C*

Answer 6

哦，看错了，不好意思。不过，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;如果把where rownum=1改成 where 1=1会怎么样呢？返回几条记录？

Answer 7

哦，看错了，不好意思。不过，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;如果把where rownum=1改成 where 1=1会怎么样呢？返回几条记录？

Answer 8

原来的写法套了几层确实有点恶心了..
只是刚开始客户那边oracle的版本恰好可以识别那种写法，看起来也没错，结果就是会把where条件过滤了..

Answer 9

1 一般很少用last_value都用first_value()  over(order by desc)因为窗口默认限制的问题。

Answer 10

select a,b,c from
(select t.*, row_number() over (partition by a order by b desc ) rn from t)
where rn = 1;  类似这样