1. select row_number() over(order by Y.kaeri_date) as RN,Y.NO,Y.YOYAKU_NO,
KAERI_DATE as kaeri_date
from BCM_YOYAKU_DATA Y
left JOIN reservemodify R ON y.yoyaku_no=R.yoyaku_no
where TRUNC(Y.kaeri_date,'dd')>=TRUNC(TO_DATE('2008-07-01','yyyy-mm-dd'),'dd')
AND TRUNC(Y.kaeri_date,'dd') <=TRUNC(TO_DATE('2008-09-30','yyyy-mm-dd'),'dd')
and Y.NO =70496
order by kaeri_date
运行后得到结果:
RN NO YOYAKU_NO KAERI_DATE
--------------------------------------------
1 70496 CY00117385 2008-7-28
2 70496 CY00118215 2008-7-30
3 70496 CY00113197 2008-8-4
4 70496 CY00116123 2008-8-8
5 70496 CY00118482 2008-8-8
6 70496 CY00117734 2008-8-9
7 70496 CY00119041 2008-8-10
8 70496 CY00116898 2008-8-13
9 70496 CY00116173 2008-8-14
10 70496 CY00119973 2008-8-14
11 70496 CY00120437_4 2008-8-14
12 70496 CY00120437_3 2008-8-14
13 70496 CY00120437_2 2008-8-14
14 70496 CY00120437_1 2008-8-14
15 70496 CY00114491_2 2008-8-14
16 70496 CY00114491_1 2008-8-14
17 70496 CY00117014 2008-8-14
18 70496 CY00119172 2008-8-14
19 70496 CY00119103 2008-8-14
20 70496 CY00117745 2008-8-14
21 70496 CY00118274_1 2008-8-14
22 70496 CY00118274_2 2008-8-14
23 70496 CY00115719 2008-8-14
24 70496 CY00119658 2008-8-14
25 70496 CY00113947 2008-8-15
26 70496 CY00120772 2008-8-23
2.
select RN,NO,yoyaku_no,kaeri_date
from (
select row_number() over(order by Y.kaeri_date) as RN,Y.NO,Y.YOYAKU_NO,
KAERI_DATE as kaeri_date
from BCM_YOYAKU_DATA Y
left JOIN reservemodify R ON y.yoyaku_no=R.yoyaku_no
where TRUNC(Y.kaeri_date,'dd')>=TRUNC(TO_DATE('2008-07-01','yyyy-mm-dd'),'dd')
AND TRUNC(Y.kaeri_date,'dd') <=TRUNC(TO_DATE('2008-09-30','yyyy-mm-dd'),'dd')
and Y.NO =70496
order by kaeri_date
) M
where (RN>(1 - 1) * 20 AND RN <=1 *20
)
RN NO YOYAKU_NO KAERI_DATE
-------------------------------
1 70496 CY00117385 2008-7-28
2 70496 CY00118215 2008-7-30
3 70496 CY00113197 2008-8-4
4 70496 CY00116123 2008-8-8
5 70496 CY00118482 2008-8-8
6 70496 CY00117734 2008-8-9
7 70496 CY00119041 2008-8-10
8 70496 CY00116898 2008-8-13
9 70496 CY00116173 2008-8-14
10 70496 CY00119973 2008-8-14
11 70496 CY00120437_1 2008-8-14
12 70496 CY00114491_2 2008-8-14
13 70496 CY00114491_1 2008-8-14
14 70496 CY00117014 2008-8-14
15 70496 CY00119172 2008-8-14
16 70496 CY00119103 2008-8-14
17 70496 CY00117745 2008-8-14
18 70496 CY00118274_1 2008-8-14
19 70496 CY00118274_2 2008-8-14
20 70496 CY00115719 2008-8-14
得到了前20条数据,但是,这里有一个问题,在26条全部数据中,
以下几条数据不见了,查询的结果是把后面的数据全部往前移了.
11 70496 CY00120437_4 2008-8-14
12 70496 CY00120437_3 2008-8-14
13 70496 CY00120437_2 2008-8-14
3.
我再换一种查询
select RN,NO,yoyaku_no,kaeri_date
from (
select row_number() over(order by Y.kaeri_date) as RN,Y.NO,Y.YOYAKU_NO,
KAERI_DATE as kaeri_date
from BCM_YOYAKU_DATA Y
left JOIN reservemodify R ON y.yoyaku_no=R.yoyaku_no
where TRUNC(Y.kaeri_date,'dd')>=TRUNC(TO_DATE('2008-07-01','yyyy-mm-dd'),'dd')
AND TRUNC(Y.kaeri_date,'dd') <=TRUNC(TO_DATE('2008-09-30','yyyy-mm-dd'),'dd')
and Y.NO =70496
order by kaeri_date
) M
where (RN>(1 - 1) * 20 AND RN <=1 *20
)
OR SUBSTR(YOYAKU_NO,1,10)='CY00120437'
RN NO YOYAKU_NO KAERI_DATE
------------------------------
1 70496 CY00117385 2008-7-28
2 70496 CY00118215 2008-7-30
3 70496 CY00113197 2008-8-4
4 70496 CY00116123 2008-8-8
5 70496 CY00118482 2008-8-8
6 70496 CY00117734 2008-8-9
7 70496 CY00119041 2008-8-10
8 70496 CY00116898 2008-8-13
9 70496 CY00116173 2008-8-14
10 70496 CY00119973 2008-8-14
11 70496 CY00120437_4 2008-8-14
12 70496 CY00120437_3 2008-8-14
13 70496 CY00120437_2 2008-8-14
14 70496 CY00120437_1 2008-8-14
15 70496 CY00114491_2 2008-8-14
16 70496 CY00114491_1 2008-8-14
17 70496 CY00117014 2008-8-14
18 70496 CY00119172 2008-8-14
19 70496 CY00119103 2008-8-14
20 70496 CY00117745 2008-8-14
得到的了1中的前20条数据.
4.我再查询20条以后的数据+ SUBSTR(YOYAKU_NO,1,10)='CY00120437'
select RN,NO,yoyaku_no,kaeri_date
from (
select row_number() over(order by Y.kaeri_date) as RN,Y.NO,Y.YOYAKU_NO,
KAERI_DATE as kaeri_date
from BCM_YOYAKU_DATA Y
left JOIN reservemodify R ON y.yoyaku_no=R.yoyaku_no
where TRUNC(Y.kaeri_date,'dd')>=TRUNC(TO_DATE('2008-07-01','yyyy-mm-dd'),'dd')
AND TRUNC(Y.kaeri_date,'dd') <=TRUNC(TO_DATE('2008-09-30','yyyy-mm-dd'),'dd')
and Y.NO =70496
order by kaeri_date
) M
where (RN>(2 - 1) * 20 AND RN <=2 *20
)
OR SUBSTR(YOYAKU_NO,1,10)='CY00120437'
得到,
这里明明可以看到,11,12,13 这三条数据是在的,为什么在
查询前20条时,又不出现,而是把后面的数据往前移呢.搞不懂.
RN NO YOYAKU_NO KAERI_DATE
-------------------------------------------
11 70496 CY00120437_4 2008-8-14
12 70496 CY00120437_3 2008-8-14
13 70496 CY00120437_2 2008-8-14
14 70496 CY00120437_1 2008-8-14
21 70496 CY00118274_1 2008-8-14
22 70496 CY00118274_2 2008-8-14
23 70496 CY00115719 2008-8-14
24 70496 CY00119658 2008-8-14
25 70496 CY00113947 2008-8-15
26 70496 CY00120772 2008-8-23