php mssql 分页SQL语句优化持续影响

2020-03-22 16:24:44

字体：大中小

来源：转载

供稿：网友

复制代码代码如下:
?php
/**
* @Filename :page.sql.html' target='_blank'>class.php
* @CreatTime :2009-01-06
* @Descrition :此类为SQL语句处理类。
* @UpdateTime-1 :null
* @Version :jswweb1.0.0
* @Author :fkedwgwy
* @Dome :
$sql//SQL语句
$allcount//总记录数
$pagesize//页面显示记录条数
$page//当前页
$sqlc= new sqlpage($sql,$allcount,$pagesize,$page);
$sql=$sqlc- getsql();
优化后的语句：
SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 270 Lsh,Ztm,Dyzrsm,Dyzzfs,Cbsm,Cbny,Ssh,Fbsl,jcsl from ts_gcb where Ssh like 'C%' order by Lsh asc) AS inner_tbl ORDER BY Lsh DESC) AS outer_tbl ORDER BY Lsh asc
*/
class sqlpage{
function sqlpage($sql,$allcount,$pagesize,$page){
$this- sql= $sql;//查询语名
$this- allcount= intval($allcount);//总记录数
$this- pagesize= intval($pagesize);//页面大小（显示记录数）
$this- page= intval($page);//当前页
$this- getpage();
$this- gettop();
}
function getpage(){ //获取当前页
$this- allpage=ceil( $this- allcount/$this- pagesize);//去当前小数的最大整数
if ($this- page=="" or $this- page $this- allpage or $this- page 0 or $this- page==0){
$this- page2=1;
}else{
$this- page2=intval($this- page);//将页码转换为数字
}
}
function gettop(){ //获取子查询2的TOP大小
if ($this- page2 $this- allpage){
$this- top2=$this- pagesize;
}else{
$this- top2=$this- allcount-$this- pagesize*($this- allpage-1);
}
}
/* function getsql(){//获取SQL语句
$this- s=preg_replace("/select/i","",$this- sql);
$this- top1=$this- pagesize*$this- page2;
$this- sql1="SELECT TOP $this- top1 $this-
if (strpos($this- sql,"asc")){//升序
$this- sql_e="select * from ( select TOP $this- top2 * FROM ( $this- sql1 ) as aSysTable ORDER BY $this- order DESC ) as bSysTable ORDER BY $this- order ASC";
}else
//$this- sql_e="select * from ( select TOP $this- top2 * FROM ( $this- sql1 ) as aSysTable ORDER BY $this- order DESC ) as bSysTable ORDER BY $this- order ASC";
if (strpos($this- sql,"desc")){//降序
$this- sql_e="select * from ( select TOP $this- top2 * FROM ( $this- sql1 ) as aSysTable ORDER BY $this- order asc ) as bSysTable ORDER BY $this- order desc";
}else{//不处理排序的情况
$this- sql_e="select * from ( select TOP $this- top2 * FROM ( $this- sql1 ) as aSysTable ORDER BY $this- order DESC ) as bSysTable ORDER BY $this- order ASC";
}
// echo $this- sql_e;
return $this- sql_e;
}*/
function getsql()
{
$sql=$this-
$this- top1=$this- pagesize*$this- page2;
$orderby = stristr($sql, 'ORDER BY');
if ($orderby !== false) {
$sort = (stripos($orderby, ' desc') !== false) ? 'desc' : 'asc';
$order = str_ireplace('ORDER BY', '', $orderby);
$order = trim(preg_replace('//bASC/b|/bDESC/b/i', '', $order));
}
$sql = preg_replace('/^SELECT/s/i', 'SELECT TOP ' . ($this- top1) . ' ', $sql);
$sql = 'SELECT * FROM (SELECT TOP ' . $this- top2 . ' * FROM (' . $sql . ') AS inner_tbl';
if ($orderby !== false) {
$sql .= ' ORDER BY ' . $order . ' ';
$sql .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC';
}
$sql .= ') AS outer_tbl';
if ($orderby !== false) {
$sql .= ' ORDER BY ' . $order . ' ' . $sort;
}
echo $sql;
return $sql;
}
}
?
更多编程语言

郑重声明：本文版权归原作者所有，转载文章仅为传播更多信息之目的，如作者信息标记有误，请第一时间联系我们修改或删除，多谢。

上一篇：php中错误及异常处理的介绍（代码示例）

下一篇：php4.0.0远程溢出源代码分析与测试程序