复制代码 代码如下:
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->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('/\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;
}
}
?>
以上就介绍了select语句 php mssql 分页sql语句优化 持续影响，包括了select语句方面的内容，希望对php教程有兴趣的朋友有所帮助。