Home > Database > Mysql Tutorial > body text

直接通过SQL取分页数据实例

WBOY
Release: 2016-06-07 17:49:04
Original
856 people have browsed it

列表页面,之前程序员用了“Wuqi.Webdiyer”的分页插件,慢,且莫明出错。看了下代码,觉得冗余太多,故决定优化一下。

从网上找了些资料,考虑直接在SQL底层,直接只取当前页的数据,再绑定,这样效率应该会高些。

核心的SQL查询语句是这样的:

 代码如下 复制代码
select top @size * from (@sqlstring) a
 where @key  and @key >= (select min(@key) from (select top @end @key from (@sqlstring) a order by @key desc) a)
 order by @key desc

 

其中:

@size:一页的数量;

@sqlstring:原始查询语句;

@key:关键字段/排序字段,它应是唯一字段;

@star:开始记录数索引,star = size*(p-1)+1;

@end:结束记录数索引,end = size*p+1;

若排序不同,则代码应做相应变化:

 代码如下 复制代码
select top @size * from (@sqlstring) a
where @key > = (select max(@key) from (select top @star @key from (@sqlstring) a order by @key asc) a)
 and @key  order by @key asc

 
我就是这样构建取数据集的方法,然后把相关参数传进来,拼接查询,返回数据集绑定即可。

这个方案暂时未发现出错,效率也有所提升。

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template