请问各位PHP怎样优化foreach 里面带sql语句的

Question

foreach ($res['xsumpre'] as $key => $value) { $fsql="select count(*) from (SELECT sum(sumprem) FROM datatom.fccont where signdate BETWEEN '$stime' AND '$etime' AND agentcode in(select agentcode from datatom.shouxian_faagenttree where agentstate not in('01','02') and OUTWORKDATE between '$stime' AND '$etime') GROUP BY agentcode having sum(sumprem) <= '$value' ORDER BY sum ) as a"; $fres=$this->pgdb->mpg_select($fsql)['0']['count']; // var_dump($value,$fres['0']['count']); $res['tate'][]=round($fres/$zres['0']['count'],4)*100; } return $res; 问题描述 接口慢返回结果样式 xsumpre 的数量和tate的返回个数要一致 { "code": 0, "data": { "xsumpre": [ 1001066, 2002132, 3003198, 4004264, 5005330, 6006396, 7007462, 8008528, 9009594, 10010660 ], "quit": 12.21, "tate": [ 0.46, 0.46, 0.46, 0.46, 0.46, 0.46, 0.46, 0.46, 0.46, 0.46 ] }, "msg": "success" }

Answer 1

看的我难受，先整理一下：

foreach($xxx as $key => $value){
    select count(*) from (
        SELECT sum(sumprem) FROM datatom.fccont where
        signdate BETWEEN '$stime' AND '$etime' AND 
        agentcode in(
            select agentcode from datatom.shouxian_faagenttree 
            where agentstate not in('01','02') and OUTWORKDATE between '$stime' AND '$etime'
        ) 
        GROUP BY agentcode 
        having sum(sumprem) <= '$value' 
        ORDER BY sum 
    ) as a
}

好了，现在清晰了一点，开始优化：
第一步先把最里面的子查询提出来：

$arr = $this->pgdb->mpg_select("select agentcode from datatom.shouxian_faagenttree 
    where agentstate not in('01','02') and OUTWORKDATE between '$stime' AND '$etime'")
    
foreach($xxx as $key => $value){
    select count(*) from (
        SELECT sum(sumprem) FROM datatom.fccont where
        signdate BETWEEN '$stime' AND '$etime' AND 
        agentcode in($arr) 
        GROUP BY agentcode having sum(sumprem) <= '$value' 
        ORDER BY sum 
    ) as a
}

首先我是认为你不用一次查完的，我提出来的第一句sql完全是重复的，所以提出在循环外面得到结果的数组再塞进in里面
第二段优化：

$arr<=db("select agentcode from datatom.shouxian_faagenttree 
    where agentstate not in('01','02') and OUTWORKDATE between '$stime' AND '$etime'")
    
foreach($xxx as $key => $value){
    select count(*) from (
        SELECT sum(sumprem) FROM datatom.fccont where
        signdate BETWEEN '$stime' AND '$etime' AND 
        agentcode in($arr) 
        GROUP BY agentcode having sum(sumprem) <= '$value' 
    ) as a
}

最后因为不了解你的表结构，还有具体需求，就不知道怎么优化了，只是去掉了order by，这个语句在这里一点用都没有，反正你最后都是要count的
就这样，觉得答案可以的话就点赞采纳吧