在sqlserver里面,例如这样的语句会报错:
select count(1) from ( select * from aaa order by productid )
除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
正则表达式去掉sql里面的order by
$sql = "select * from (select * from t_x Order by ( id) Asc , xh desc
union all
select * from t_y order by xsdfx desc) sss order by (SDf) group by ddk
UNION all select * from xx where xid>100 ) as t";
echo '$sql='.$sql.'<br/><br/> ';
$regex = '/(order\s*by\s*.*desc)|(order\s*by\s*.*asc)|(order\s*by\s*\S+)/i';
preg_match_all($regex, $sql . " ", $matches);
//print_r($matches); echo '<br/>';
$sql = str_ireplace($matches[0], ' ', $sql);
echo '去掉order by后:'.$sql;
但是,如果 order by 后面还有 case when 之类的复杂语句的话就难处理了。
另一种方法是 在 select 后 加上 top 100 percent
select count(1) from ( select top 100 percent * from aaa order by productid )