分页SQL语句

noel0217

方案1)
蔽端 由于有个 not in 所有访问在数量的数据库时.速度很慢

     sql="select top 每页显示条数 * from 表名 where id not in      (select top "+(当前页-1)*每页显示条数   +"               id            from     表名 where ";
          sql2=" "+major+" and "+empid+" and emp_state='"+state+"' and "+empname;//条件(可选 )
            //" order by emp_id) and ";+" order by emp_id "+sort;//排序规则
            sql=sql+sql2+" order by emp_id "+sort+") and "+sql2+" order by emp_id "+sort;合并

如下例:

               select top 15 * from employee where id not in(select top 0 id from employee where
               1=1 and emp_id like '%0801%' and emp_state='1' and emp_name like '%小王%'
                 order by emp_id asc) and
               1=1 and emp_id like '%0801%' and emp_state='1' and emp_name like '%小王%'
               order by emp_id asc

此类SQL在ACCESS数据库中不适用,因为当ACCESS数据记录超过1W时,分页速度下降历害.(access测试过)

方案2)

通用分页SQL (也适用小型数据库 access测试通过)

不过有个蔽端,当数据库有十条记录时,页面显示三条时,则最后一个页面不是显示一条,而是三条,我观插后发现这三条是最后三条

升序分页:

   select * from ( select top 5 * from (select top 10 * from log   order by logid asc ) order by logid desc ) order by logid asc

降序分页:

select * from ( select top 5 * from (select top 10 * from log   order by logid desc) order by logid asc) order by logid desc

方案3)      蔽端 当删除一些记录时,查询会出现我错误, 圣只有时得不到结果,因为这是靠主键ID来进行排序.删除记录就相当于重新删除记录,解决办法就是:对数剧库理的记录进行ID重新排序

access 测试通过.SQL SERVER 测试通过

//升序

if (select.getCurrent() == 1)//当前页为1
    sl = "select top "+每页显示条数+" * from test where " + 条件;
else
    sl = "select top "+每页显示条数+" * from test where id>(select max(id) from (select top "+((当前页 - 1)*每页显示 条数)+" * from test where "+条件+") as t)";
   }


//降序

if (select.getCurrent() == 1)//当前页为1
    sl = "select top "+每页显示条数+" * from logdata where " +条件+"order by logid desc";
else
      sl ="select top "+每页显示条数+" * from logdata where id<(select min(id) from (select top "+    ((当前页 - 1)*每页显示条数)+" * from logdata where "+条件+" order by iddesc) as t) order by logid desc";
   }