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;
問題描述
接口慢返回結(jié)果樣式
xsumpre 的數(shù)量和tate的返回個(gè)數(shù)要一致
{
"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"
}
2 回答
侃侃爾雅
TA貢獻(xiàn)1801條經(jīng)驗(yàn) 獲得超16個(gè)贊
看的我難受，先整理一下：
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
}好了，現(xiàn)在清晰了一點(diǎn)，開始優(yōu)化：
第一步先把最里面的子查詢提出來：
$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
}首先我是認(rèn)為你不用一次查完的，我提出來的第一句sql完全是重復(fù)的，所以提出在循環(huán)外面得到結(jié)果的數(shù)組再塞進(jìn)in里面
第二段優(yōu)化：
$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
}最后因?yàn)椴涣私饽愕谋斫Y(jié)構(gòu)，還有具體需求，就不知道怎么優(yōu)化了，只是去掉了order by，這個(gè)語句在這里一點(diǎn)用都沒有，反正你最后都是要count的
就這樣，覺得答案可以的話就點(diǎn)贊采納吧
- 2 回答
- 0 關(guān)注
- 422 瀏覽
添加回答
舉報(bào)
0/150
提交
取消