三种数据库的分页语句

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

sql server方案1：

    select top 10 * from twhere id not in (select top 30 id from t order by id ) orde by id

sql server方案2：

    select top 10 * from twhere id in (select top 40 id from t order by id) order by id desc

 

mysql方案：select * from t order by id limit 30,10

 

cp 是页数

ls  是每页的条数

 

 this.pstmt.setInt(2, (cp-1) * ls) ;
  this.pstmt.setInt(3, ls) ;

 

oracle方案：select * from (select rownum r,* from t where r<=40) wherer>30

 

 

String sql = " SELECT * FROM ( "

                + " SELECT mid,name,birthday,age,content,ROWNUM rn "

                + " FROM member WHERE name LIKE ? AND  ROWNUM<=? ) temp "

                + " WHERE temp.rn>? ";

 

pstmt.setInt(2, cp* ls);
  pstmt.setInt(3, (cp- 1) * ls);

 

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

pageSize=20;

pageNo = 5;

 

1.分页技术1（直接利用sql语句进行分页，效率最高和最推荐的）

 

mysql:sql = "select * from articleslimit " + (pageNo-1)*pageSize + "," + pageSize;

oracle: sql = "select * from " +

                                                        "(selectrownum r,* from " +

                                                               "(select* from articles order by postime desc)" +

                                                        "whererownum<= " + pageNo*pageSize +") tmp " +

                                                 "wherer>" + (pageNo-1)*pageSize;

注释：第7行保证rownum的顺序是确定的，因为oracle的索引会造成rownum返回不同的值

简洋提示：没有orderby时，rownum按顺序输出，一旦有了order by，rownum不按顺序输出了，这说明rownum是排序前的编号。如果对order by从句中的字段建立了索引，那么，rownum也是按顺序输出的，因为这时候生成原始的查询结果集时会参照索引表的顺序来构建。

 

sqlserver:sql = "select top 10 * fromid not id(select top " + (pageNo-1)*pageSize + "id fromarticles)"

 

 

 

 

下面是大数据量下的解决方案

 

 

sql server:

       Stringsql =    "selecttop " + pageSize + " * from students where id not in" +

 

 "(select top " + pageSize *(pageNumber-1) + " id from students order by id)" +

 

 "order by id";

 

我在SQL下用的一个分页，成功

 

    public List findAll(int currentPage, int lineSize, String keyWord) throws Exception {
        List all = new ArrayList();
        int aaa = (currentPage - 1) * lineSize;
        String sql = "SELECT TOP " + lineSize + " ID,stuID,stuName,enterTime,stuYear,address,tellphone,stuclass,remark FROM studentinfo WHERE (ID LIKE ? OR stuID LIKE ? OR stuName LIKE ?  OR enterTime LIKE ?  OR stuYear LIKE ?  OR address LIKE ?  OR tellphone LIKE ?  OR stuclass LIKE ?  OR remark LIKE ?) AND ID NOT IN (SELECT TOP " + aaa + " ID FROM studentinfo ORDER BY ID DESC) ORDER BY ID DESC ";

        try {
            this.pstmt = this.conn.prepareStatement(sql);
            this.pstmt.setString(1, "%" + keyWord + "%");
            this.pstmt.setString(2, "%" + keyWord + "%");
            this.pstmt.setString(3, "%" + keyWord + "%");
            this.pstmt.setString(4, "%" + keyWord + "%");
            this.pstmt.setString(5, "%" + keyWord + "%");
            this.pstmt.setString(6, "%" + keyWord + "%");
            this.pstmt.setString(7, "%" + keyWord + "%");
            this.pstmt.setString(8, "%" + keyWord + "%");
            this.pstmt.setString(9, "%" + keyWord + "%");
            ResultSet rs = this.pstmt.executeQuery();
            Student stu = null;
            while (rs.next()) {
                stu = new Student();
                stu.setID(rs.getInt(1));
                stu.setStuID(rs.getString(2));
                stu.setStuName(rs.getString(3));
                stu.setEnterTime(rs.getString(4));
                stu.setStuYear(rs.getString(5));
                stu.setAddress(rs.getString(6));
                stu.setTellphone(rs.getString(7));
                stu.setStuclass(rs.getString(8));
                stu.setRemark(rs.getString(9));
                all.add(stu);
            }
            rs.close();
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                this.pstmt.close();
            } catch (Exception e) {
            }
        }
        return all;
    }

 

 

 

 

 

mysql:

 

       Stringsql =   "select* from students order by id limit " + pageSize*(pageNumber-1) +"," + pageSize;

      

oracle:

 

       Stringsql =    "select * from " + 

        (select *,rownum rid from (select * fromstudents order by postime desc) where rid<=" + pagesize*pagenumber +") as t" +

        "where t>" +pageSize*(pageNumber-1);