SqlServer2000 与oracle 分页

最新推荐文章于 2025-05-13 22:11:30 发布

原创最新推荐文章于 2025-05-13 22:11:30 发布 · 494 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #oracle #string #sql #query #user

数据库专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一种在Oracle和SQL Server 2000中实现分页查询的方法。通过使用特定于数据库的功能，如ROWNUM和TOP关键字，来限制查询结果集的大小，从而有效地进行分页操作。

//oracle
   public static String query(String sql,int page,int length){
          if( page<=0 ) {
            page = 1;
          }
          int begin = (page-1)*length;
          int end = page*length;
          String qs =" SELECT * FROM ( SELECT row_.*, ROWNUM rownum_ FROM ( "
                     + sql
                     + " ) row_ WHERE ROWNUM <= " + end + ") WHERE rownum_ >" +begin;
         return qs;
      }

/**

   * 分页（sqlserver2000）
   * 例： "SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 10 id,name,age "+
          " FROM user where 1=1 order by id desc ) TB2 order by id asc) TB3 order by id desc ";
   * @param sqlFields 搜索字段
   * @param tableName   表名
   * @param where   where条件
   * @param orderby   排序(写上desc，asc)
   * @param page   当前页
   * @param length   显示条数
   * @return
   */
   public static String queryBySqlServer(String sqlFields,String tableName ,String where,String orderby,int page,int length){
          if( page<=0 ) {
            page = 1;
          }
          String cutorderby = "";
          orderby = orderby.toLowerCase();
          if(orderby.contains("desc")){
              cutorderby = orderby.replace("desc", "asc");
          }else{
              cutorderby = orderby.replace("asc", "desc");
          }
          StringBuffer sb = new StringBuffer();
          sb.append("SELECT * FROM (SELECT TOP ");
          sb.append(length);
          sb.append(" * FROM (SELECT TOP ");
          sb.append(page*length).append(" ");
          sb.append(sqlFields).append(" ");
          sb.append(" FROM ");
          sb.append(tableName).append(" ");
          sb.append(where).append(" ");
          sb.append(orderby).append(" ");
          sb.append(" ) TB2 ");
          sb.append(cutorderby).append(" ");
          sb.append(" ) TB3 ");
          sb.append(orderby);
          return sb.toString();

      }