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

小德 2012-08-27 04:09:24
各位兄弟，以下是我遇到的问题
传统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 個資料列.
...全文
262 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
黑色的熊猫 2012-08-28
  • 打赏
  • 举报
 回复
select a,b,c from
(select t.*, row_number() over (partition by a order by b desc ) rn from t)
where rn = 1; 类似这样。。。
forgetsam 2012-08-28
  • 打赏
  • 举报
 回复
1 一般很少用last_value都用first_value() over(order by desc)

因为窗口默认限制的问题。
小德 2012-08-27
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 的回复:]

俺的oracle还真不认识你的那个order by子查询的SQL，改写了下代码，执行的结果正常。
SELECT PMCEN FROM
(
SELECT PMCEN
FROM txd000paa41,T
WHERE txd000paa41.CO=T.CO
AND txd000paa41.MPID=T.……
[/Quote]
原来的写法套了几层确实有点恶心了..
只是刚开始客户那边oracle的版本恰好可以识别那种写法，看起来也没错，结果就是会把where条件过滤了..
fw0124 2012-08-27
  • 打赏
  • 举报
 回复
哦，看错了，不好意思。

不过，

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会怎么样呢？返回几条记录？
fw0124 2012-08-27
  • 打赏
  • 举报
 回复
哦，看错了，不好意思。

不过，

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会怎么样呢？返回几条记录？
xiaobn_cn 2012-08-27
  • 打赏
  • 举报
 回复
俺的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*
小德 2012-08-27
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 的回复:]
11 ORDER BY Length(ORGNLVDP) DESC

这里指定了 desc

last_value()那里没有指定desc阿
[/Quote]
因为应经是用last_value()函数了..不用指定desc了..
小德 2012-08-27
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]
没看全，不过建议如下，你可以试试


SQL code

(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
……
[/Quote]
这样的话，其实order by就相当于没用了吧..
fw0124 2012-08-27
  • 打赏
  • 举报
 回复
11 ORDER BY Length(ORGNLVDP) DESC

这里指定了 desc

last_value()那里没有指定desc阿
kingstarer 2012-08-27
  • 打赏
  • 举报
 回复
没看全，不过建议如下，你可以试试



(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)
Hive分析函数和窗口函数
Hive分析函数和窗口函数在Hive 0.11之后支持的，扫描多个输入的行计算每行的结果。通常和OVER,PARTITION ...和传统的分组结果不一样，传统的结果每组中只有一个结果。分析函数的结果会出现多次，和每条记录都连接输出。
Oracle 分析函数与聚合函数（重点理解）
一、Oracle分析函数的语法与作用：   rank ( ) over ( [query_partition_clause]order_by_clause ) dense_rank ( ) over ([query_partition_clause] order_by_clause ) rownumber ( ) over ( [query_partition
hive 各种by相关的介绍
hive中的order by和传统sql中的order by 一样，会对数据做全局排序，加上排序，会新启动一个jod进行排序，会把所有数据放到同一个reduce中进行处理，不管数据多少， 不管文件多少，都启用一个reduce进行处理。 注意...
Order By文件排序和索引排序
在MySQL中的ORDER BY有两种排序实现方式: 1、利用有序索引获取有序数据 2、文件排序 在使用explain分析查询的时候，利用有序索引获取有序数据显示Using index。而文件排序显示Using filesort。 1.利用有序...
Oracle所有分析函数
Oracle分析函数   Oracle分析函数——函数列表   SUM ：该函数计算组中表达式的累积和 MIN ：在一个组中的数据窗口中查找表达式的最小值 MAX ：在一个组中的数据窗口中查找表达式的最大值 AVG ：用于...
Oracle

17,086

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章