SQL语句:
SELECT *
FROM (SELECT *
FROM (SELECT P.*,
ROW_NUMBER() OVER(PARTITION BY ACCTNO ORDER BY SORTNO) AS RK
FROM TEST_FDM_KNA_ORBK P
WHERE P.AS_OF_DATE = TO_DATE(20120131, 'YYYYMMDD'))
WHERE RK = 1) B
WHERE B.ACCTNO IN
(SELECT CASE
WHEN SUM(B.ACCTFS) OVER(PARTITION BY ACCTNO) = COUNT(1)
OVER(PARTITION BY ACCTNO) THEN
ACCTNO
END
FROM TEST_FDM_KNA_ORBK
WHERE AS_OF_DATE = TO_DATE(20120131, 'YYYYMMDD')
AND MINUS_ONLNBAL <> 0);
执行计划:
Plan hash value: 121659835
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 6 (34)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | VIEW | | 12 | 480 | 3 (34)| 00:00:01 |
|* 3 | WINDOW SORT PUSHED RANK| | 12 | 564 | 3 (34)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TEST_FDM_KNA_ORBK | 12 | 564 | 2 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | WINDOW SORT | | 1 | 44 | 3 (34)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | TEST_FDM_KNA_ORBK | 1 | 44 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (<not feasible>)
2 - filter("RK"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "ACCTNO" ORDER BY "SORTNO")<=1)
4 - filter("P"."AS_OF_DATE"=TO_DATE(' 2012-01-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
5 - filter(CASE SUM(TO_NUMBER(:B1)) OVER ( PARTITION BY "ACCTNO") WHEN COUNT(1)
OVER ( PARTITION BY "ACCTNO") THEN "ACCTNO" END =:B2)
7 - filter("AS_OF_DATE"=TO_DATE(' 2012-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "MINUS_ONLNBAL"<>0)
Note
------ dynamic sampling used for this statement (level=2)其中CASE WHEN SUM(B.ACCTFS) OVER(PARTITION BY ACCTNO) = COUNT(1)
OVER(PARTITION BY ACCTNO) THEN
ACCTNO
END
是:ACCTFS只为0,1。就是要找到账号的ACCTFS都是1
有2个问题:
1.其实在CASE WHEN SUM(B.ACCTFS) OVER(PARTITION BY ACCTNO) = COUNT(1)
OVER(PARTITION BY ACCTNO) THEN
ACCTNO
END
中B.ACCTFS是写错了的,应该是ACCTFS。B是FROM后那个别名,但在这里为什么不会报错了 。而且在执行计划中 5 - filter可以看到B.ACCTFS变成了:B1,这个:B1是什么呢??
2.还是在 5 - filter 中这个=:B2是什么呢???
SELECT *
FROM (SELECT *
FROM (SELECT P.*,
ROW_NUMBER() OVER(PARTITION BY ACCTNO ORDER BY SORTNO) AS RK
FROM TEST_FDM_KNA_ORBK P
WHERE P.AS_OF_DATE = TO_DATE(20120131, 'YYYYMMDD'))
WHERE RK = 1) B
WHERE B.ACCTNO IN
(SELECT CASE
WHEN SUM(B.ACCTFS) OVER(PARTITION BY ACCTNO) = COUNT(1)
OVER(PARTITION BY ACCTNO) THEN
ACCTNO
END
FROM TEST_FDM_KNA_ORBK
WHERE AS_OF_DATE = TO_DATE(20120131, 'YYYYMMDD')
AND MINUS_ONLNBAL <> 0);
执行计划:
Plan hash value: 121659835
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 6 (34)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | VIEW | | 12 | 480 | 3 (34)| 00:00:01 |
|* 3 | WINDOW SORT PUSHED RANK| | 12 | 564 | 3 (34)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TEST_FDM_KNA_ORBK | 12 | 564 | 2 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | WINDOW SORT | | 1 | 44 | 3 (34)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | TEST_FDM_KNA_ORBK | 1 | 44 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (<not feasible>)
2 - filter("RK"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "ACCTNO" ORDER BY "SORTNO")<=1)
4 - filter("P"."AS_OF_DATE"=TO_DATE(' 2012-01-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
5 - filter(CASE SUM(TO_NUMBER(:B1)) OVER ( PARTITION BY "ACCTNO") WHEN COUNT(1)
OVER ( PARTITION BY "ACCTNO") THEN "ACCTNO" END =:B2)
7 - filter("AS_OF_DATE"=TO_DATE(' 2012-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "MINUS_ONLNBAL"<>0)
Note
------ dynamic sampling used for this statement (level=2)其中CASE WHEN SUM(B.ACCTFS) OVER(PARTITION BY ACCTNO) = COUNT(1)
OVER(PARTITION BY ACCTNO) THEN
ACCTNO
END
是:ACCTFS只为0,1。就是要找到账号的ACCTFS都是1
有2个问题:
1.其实在CASE WHEN SUM(B.ACCTFS) OVER(PARTITION BY ACCTNO) = COUNT(1)
OVER(PARTITION BY ACCTNO) THEN
ACCTNO
END
中B.ACCTFS是写错了的,应该是ACCTFS。B是FROM后那个别名,但在这里为什么不会报错了 。而且在执行计划中 5 - filter可以看到B.ACCTFS变成了:B1,这个:B1是什么呢??
2.还是在 5 - filter 中这个=:B2是什么呢???
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货