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

小德 小德 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
各位兄弟,以下是我遇到的问题
传统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 個資料列.


...全文

相关推荐

SQL窗口函数五大应用场景详解[代码]

本文详细介绍了SQL窗口分析函数的五大应用场景:聚合、排序、极值、移动和切片。窗口函数与普通聚合函数不同,它对每个组返回多行,并可以指定数据窗口大小进行统计分析。文章首先解释了窗口函数的基本语法,包括函数、partition by、order by和rows between等关键部分。然后通过某店铺2019年销量数据表sale_detail,逐一演示了sum、count、avg、max/min等聚合函数在窗口中的使用,以及row_number、rank、dense_rank等排序函数,first_value、last_value等极值函数,lag、lead等移动函数,和ntile切片函数的具体应用。每个场景都配有SQL示例代码和结果说明,帮助读者理解如何利用窗口函数解决复杂的数据分析问题,提升SQL查询效率。

XX56789

Oracle专题培训.docx

Oracle专题培训.docx

cailibin

大数据分析师认证-样题--0814-v0.2.pdf

大数据分析师认证-样题--0814-v0.2.pdf

m0_62051506

MySQL取整与四舍五入函数[代码]

本文详细介绍了MySQL中常用的取整和四舍五入函数,包括CEIL()、CEILING()、FLOOR()、ROUND()和TRUNCATE()。CEIL()和CEILING()功能相同,返回不小于参数的最小整数值,例如CEIL(9.12)返回10,CEIL(-9.12)返回-9。FLOOR()返回不大于参数的最大整数值,如FLOOR(9.12)返回9,FLOOR(-9.12)返回-10。ROUND()用于四舍五入,可指定小数位数D,D为负时对小数点左边进行舍入,如ROUND(23.298, -1)返回20。TRUNCATE()直接截断到指定小数位,不进行四舍五入,如TRUNCATE(9.298, 1)返回9.2。文章还提到在MySQL 8.0.21及以后版本中,TRUNCATE()返回的数据类型与ROUND()相同。每个函数都附带了正负数示例,并说明了D的最大绝对值为30,超过会被截断。

Melon

《sql入门使用与高级使用篇》

sql的用途,特点,原理及如何轻松地使用

lihuamin510870
评论 10
收藏 已收藏
分享
返回顶部
小德
Oracle 17,134 成员 55,258 内容