sql你server，mysql，oracle的分页语句

取出sql表中第31到40的记录（以自动增长ID为主键）

1 sql server方案1：
2     select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id
3 sql server方案2：
4     select top 10 * from t where id in (select top 40 id from t order by id) order by id desc
5 
6 mysql方案：select * from t order by id limit 30,10
7 
8 oracle方案：select * from (select rownum r,* from t where r<=40) where r>30

--------------------待整理进去的内容-------------------------------------

 1 pageSize=20;
 2 pageNo = 5;
 3 
 4 1.分页技术1（直接利用sql语句进行分页，效率最高和最推荐的）
 5 
 6 mysql:sql = "select * from articles limit " + (pageNo-1)*pageSize + "," + pageSize;
 7 oracle: sql = "select * from " +
 8      "(select rownum r,* from " +
 9      "(select * from articles order by postime desc)" +
10      "where rownum<= " + pageNo*pageSize +") tmp " +
11      "where r>" + (pageNo-1)*pageSize;
12 注释：第7行保证rownum的顺序是确定的，因为oracle的索引会造成rownum返回不同的值
13 简洋提示：没有order by时，rownum按顺序输出，一旦有了order by，rownum不按顺序输出了，这说明rownum是排序前的编号。如果对order by从句中的字段建立了索引，那么，rownum也是按顺序输出的，因为这时候生成原始的查询结果集时会参照索引表的顺序来构建。
14 
15 sqlserver:sql = "select top 10 * from id not id(select top " + (pageNo-1)*pageSize + "id from articles)"
16 
17 DataSource ds = new InitialContext().lookup(jndiurl);
18 Connection cn = ds.getConnection();
19 //"select * from user where id=?"  --->binary directive
20 PreparedStatement pstmt = cn.prepareSatement(sql);
21 ResultSet rs = pstmt.executeQuery()
22 while(rs.next())
23 {
24     out.println(rs.getString(1));
25 }
26 
27 2.不可滚动的游标
28 pageSize=20;
29 pageNo = 5;
30 cn = null
31 stmt = null;
32 rs = null;
33 try
34 {
35 sqlserver:sql = "select  * from articles";
36 
37 DataSource ds = new InitialContext().lookup(jndiurl);
38 Connection cn = ds.getConnection();
39 //"select * from user where id=?"  --->binary directive
40 PreparedStatement pstmt = cn.prepareSatement(sql);
41 ResultSet rs = pstmt.executeQuery()
42 for(int j=0;j<(pageNo-1)*pageSize;j++)
43 {
44     rs.next();
45 }
46 
47 int i=0;
48 
49 while(rs.next() && i<10)
50 {
51     i++;
52     out.println(rs.getString(1));
53 }
54 }
55 cacth(){}
56 finnaly
57 {
58     if(rs!=null)try{rs.close();}catch(Exception e){}
59     if(stm.........
60     if(cn............
61 }
62 
63 3.可滚动的游标
64 pageSize=20;
65 pageNo = 5;
66 cn = null
67 stmt = null;
68 rs = null;
69 try
70 {
71 sqlserver:sql = "select  * from articles";
72 
73 DataSource ds = new InitialContext().lookup(jndiurl);
74 Connection cn = ds.getConnection();
75 //"select * from user where id=?"  --->binary directive
76 PreparedStatement pstmt = cn.prepareSatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,...);
77 //根据上面这行代码的异常SQLFeatureNotSupportedException，就可判断驱动是否支持可滚动游标
78 
79 ResultSet rs = pstmt.executeQuery()
80 rs.absolute((pageNo-1)*pageSize)
81 int i=0;
82 while(rs.next() && i<10)
83 {
84     i++;
85     out.println(rs.getString(1));
86 }
87 }
88 cacth(){}
89 finnaly
90 {
91     if(rs!=null)try{rs.close();}catch(Exception e){}
92     if(stm.........
93     if(cn............
94 }