with ties语法

botang

1. create table t071fi03_with_ties  ( a number , b varchar2(20));
   
2. 
   
3. insert into t071fi03_with_ties values (1,'B');
   
4. 
   
5. insert into t071fi03_with_ties values (1,'B');
   
6. 
   
7. insert into t071fi03_with_ties values (1,'B');
   
8. 
   
9. insert into t071fi03_with_ties values (2,'B');
   
10. 
    
11. insert into t071fi03_with_ties values (2,'B');
    
12. 
    
13. insert into t071fi03_with_ties values (2,'B');
    
14. 
    
15. insert into t071fi03_with_ties values (2,'B');
    
16. 
    
17. insert into t071fi03_with_ties values (2,'B');
    
18. 
    
19. insert into t071fi03_with_ties values (2,'B');
    
20. 
    
21. insert into t071fi03_with_ties values (2,'B');
    
22. 
    
23. insert into t071fi03_with_ties values (2,'B');
    
24. 
    
25. insert into t071fi03_with_ties values (2,'B');
    
26. 
    
27. insert into t071fi03_with_ties values (2,'B');
    
28. 
    
29. insert into t071fi03_with_ties values (2,'B');
    
30. 
    
31. commit;
    
32. 
    
33. select  * from t071fi03_with_ties;
    
34. 
    
35. begin
    
36.   for i in 3..100
    
37.   loop
    
38.     insert into t071fi03_with_ties values    (i, 'X');
    
39.   end loop;
    
40.   commit;
    
41. end;
    
42. /
    
43. 
    
44. select  * from t071fi03_with_ties
    
45.   order by a
    
46.   offset 2 rows
    
47.   fetch next 5 rows with ties;
    
48.   
    
49. select  * from t071fi03_with_ties
    
50.   order by a
    
51.   offset 2 rows
    
52.   fetch next 5 rows only;
    
53.   
    
54. select  * from t071fi03_with_ties
    
55.   order by a
    
56.   offset 2 rows
    
57.   fetch next 5 percent rows with ties;
    
58. 
    
59. select  * from t071fi03_with_ties
    
60.   order by a
    
61.   offset 2 rows
    
62.   fetch next 5 percent rows only;
    
63.   
    
64.   select  * from t071fi03_with_ties
    
65.   order by a
    
66.   offset 2 rows
    
67.   fetch next 10 percent rows with ties;
    
68. 
    
69. select  * from t071fi03_with_ties
    
70.   order by a
    
71.   offset 2 rows
    
72.   fetch next 10 percent rows only;
    
73.   
    

复制代码