解决mssql中不能用limit的问题
呵呵,写了个函数,将mssql中不能用limit的问题解决了,一般的是都没什么问题了,如果sql语句太高级,可能就没有用了。
function mssql_select($sql)
{
if(eregi( " limit ", $sql))
{
//去连续空格
while(eregi( " ", $sql))
{
$sql = str_replace( " ", " ",$sql);
}
$sql_array = explode( " ",$sql);
//取得部分重要的数组索引
$i = 0 ;
while($sql_array[$i])
{
if($sql_array[$i]== "from ")
{
$from_id = $i;
}
if($sql_array[$i]== "limit ")
{
$limit_id = $i;
}
if($sql_array[$i]== "order ")
{
$order_id = $i;
}
$i++;
}
$last_id = $i-1;
$two_num = explode( ", ",$sql_array[$limit_id+1]);
$totle_num = $two_num[0]+$two_num[1];
//当sql语句用倒序排列的时候
if(eregi( " desc ", $sql))
{
$sql_return = "select ";
for($i=1;$i <=$from_id;$i++)
{
$sql_return .= $sql_array[$i];
$sql_return .= " ";
}
$sql_return .= " ( ";
$sql_return .= "select top ";
$sql_return .= $two_num[1]. " ";
//echo $from_id;
for($i=1;$i <=$from_id;$i++)
{
$sql_return .= $sql_array[$i];
$sql_return .= " ";
}
$sql_return .= " ( ";
$sql_return .= "select top ";
$sql_return .= $totle_num. " ";
for($i=1;$i <$limit_id;$i++)
{
$sql_return .= $sql_array[$i];
$sql_return .= " ";
}
$sql_return .= " ) as totleResult ";
for($i=$from_id+2;$i <$limit_id;$i++)
{
if($sql_array[$i] == "desc ")
continue;
$sql_return .= $sql_array[$i];
$sql_return .= " ";
}
$sql_return .= " ) as allResult ";
for($i=$from_id+2;$i <$limit_id;$i++)
{
$sql_return .= $sql_array[$i];
$sql_return .= " ";
}
}
//顺序排列
if(!eregi( " desc ", $sql))
{
$sql_return = "select ";
for($i=1;$i <=$from_id;$i++)
{
$sql_return .= $sql_array[$i];
$sql_return .= " ";
}
$sql_return .= " ( ";
$sql_return .= "select top ";
$sql_return .= $two_num[1]. " ";
for($i=1;$i <=$from_id;$i++)
{
$sql_return .= $sql_array[$i];
$sql_return .= " ";
}
$sql_return .= " ( ";
$sql_return .= "select top ";
$sql_return .= $totle_num. " ";
for($i=1;$i <$limit_id;$i++)
{
$sql_return .= $sql_array[$i];
$sql_return .= " ";
}
$sql_return .= " ) as totleResult ";
for($i=$from_id+2;$i <$limit_id;$i++)
{
$sql_return .= $sql_array[$i];
$sql_return .= " ";
if($i == $order_id+2)
{
$sql_return .= "desc ";
}
}
$sql_return .= " ) as allResult ";
for($i=$from_id+2;$i <$limit_id;$i++)
{
$sql_return .= $sql_array[$i];
$sql_return .= " ";
}
}
return $sql_return;
}
else
return $sql;
}
输入:
$sql = "select * from chenhua_news order by news_id desc limit 5,4 ";
输出:
select * from ( select top 4 * from ( select top 9 * from chenhua_news order by news_id desc ) as totleResult order by news_id ) as allResult order by news_id desc