分页SQL语句
方案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";
}
页:
[1]