传统order by和分析函数结果不一致
各位兄弟，以下是我遇到的问题
传统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
CORE10.2.0.1.0Production
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 料列.
[解决办法]
没看全，不过建议如下，你可以试试
- 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 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)
[解决办法]
11 ORDER BY Length(ORGNLVDP) DESC
这里指定了 desc
last_value()那里没有指定desc阿
[解决办法]
俺的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*
[解决办法]
哦，看错了，不好意思。
不过，
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会怎么样呢？返回几条记录？
[解决办法]
哦，看错了，不好意思。
不过，
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会怎么样呢？返回几条记录？
[解决办法]
1 一般很少用last_value都用first_value() over(order by desc)
因为窗口默认限制的问题。
[解决办法]
select a,b,c from
(select t.*, row_number() over (partition by a order by b desc ) rn from t)
where rn = 1; 类似这样。。。