select a,
(select b from c where ...) as d,
e
from f,(select j from h where ...) as i
where ....我要把外层select ... from ...中,select后面的内容替换掉,而保留from后面的内容。最后变成:select count(*)
from f,(select j from h where ...) as i
where ....其实这相当于xml/html节点的替换,类似递归问题,想了很久也没想到解决方法。
select count(*) from (-- 你的sql --
select a,
(select b from c where ...) as d,
e
from f,(select j from h where ...) as i
where ....) tmp如果你非要严格做替换, 要做语法分析, 考虑到单双引号, 括号等等....
function rebuildSqlString($SqlString){
if(preg_match("/^select\s.+?\sfrom.*$/imus",$SqlString) > 0){ //计算需要替换的sub select...from...数量
function subSelectNum($sel){
//计算子查询位置和长度
preg_match_all('/\(select[\s\S]+?from[^\)]*?\)/imus',$sel,$selFrom,PREG_OFFSET_CAPTURE);
foreach($selFrom[0] as $key=>$val){
//只取子查询字符串的开始和结束位置
$subSelect[$key] = array($val[1],$val[1]+strlen($val[0]));
}
//获取每个' from '的位置
preg_match_all('/\sfrom\s/imus',$sel,$froms,PREG_OFFSET_CAPTURE);
$val = null;
$count = 0; //需要替换多少个sub select
foreach($froms[0] as $key=>$val){
$inSubSelect = false;
foreach($subSelect as $v){
//比较字符串位置关系,如果' from '在sub select中则退出
if($val[1] > $v[0] && $val[1] < $v[1]){
$inSubSelect = true;
$count++;
break;
}
}
//如果没发现子from在任何sub select中,则说明该'from'位于最外层,这时完全退出foreach
if(!$inSubSelect){
break;
}
}
return $count;
}
$count = subSelectNum($SqlString);
//把select...from...中位于select...from中间的子句全部替换掉
while($count){
$SqlString = preg_replace('/\(select[\s\S]+?from[^\)]*?\)/imus','',$SqlString,1);
$count--;
}
$SqlString = preg_replace('/^select([\s\S]+?)from(.*?)$/iums',"SELECT count(*) FROM $2",$SqlString,1);
}
return $SqlString;
}
如果是做字符串替换,可以这么写$s = <<< TXT
select a,
(select b from c where ...) as d,
e
from f,(select j from h where ...) as i
where ....
TXT;$ar = preg_split('/(\(?\bselect\b|\bfrom\b)/i', $s, -1, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE);$n = 0;
$st = array();
for($i=0; $i<count($ar); $i++) {
$t = strtolower($ar[$i]);
if($t == 'select' || $t == '(select') {
$st[] = $i;
}
if($t == 'from') {
if(count($st) == 1) break;
array_pop($st);
}
}
for($i--; $i>$st[0]+1; $i--) unset($ar[$i]);
$ar[$st[0]+1] = " count(*)\n";
echo join('', $ar);select count(*)
from f,(select j from h where ...) as i
where ....