select語句 php mssql 分頁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/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,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh,Fbny,Ssh, ,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 sqlphp
/***/
class sqlphp
/***/
class sqlphp
/***/
class sqlphp
/***/
class sqlphp
/***/
class sqlphp
/***/
class sqlphp
/***/
class sqlphp
/***/
class sqlphp
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-> pagepage==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('/bASCb|bDESCb/i', '' , $order)); }

$sql = preg_replace('/^SELECTs/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; } } ?> 以上就介紹了select語句 php mssql 分頁SQL語句優化 持續影響，包含了select語句方面的內容，希望對PHP教程有興趣的朋友有所幫助。