php分页查询mssql保留条件,php mssql 分页SQL语句优化 持续影响

经过SQL优化后的分面查询速度能够得到大幅提高。

/**

* @Filename :page.sql.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->page2allpage){                                                                       $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->s";                                                                    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->sql;        $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;                    }}?>