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 }