请问各位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的就这样，觉得答案可以的话就点赞采纳吧