oracle group by 函数单组分数函数怎么修改
0
悬赏园豆：10
[待解决问题]
select * from (
select
c.id as glsid,c.name as glsmz,a.id as dcxzid,b.name as dcxzmz,a.name as dcmz,
d.lsds,d.lsjs,d.dpje,d.lsje,d.pjzk,
nvl(case when sum(d.lsds)=0 then null else round(sum(d.lsjs)/sum(d.lsds),2) end,null) as ldv,
nvl(case when sum(d.lsds)=0 then null else round(sum(d.lsje)/sum(d.lsds),2) end,null) as kdj,
nvl(case when sum(d.lsjs)=0 then null else round(sum(d.lsje)/sum(d.lsjs),2) end,null) as jdj
from
C_STORE a
left join C_BLOCK c on c.id=A.C_BLOCK_ID
left join C_STOREKIND b on a.C_STOREKIND_ID=b.id
left join (
select a.C_STORE_ID,a.lsds,a.lsjs,a.dpje,a.lsje,a.pjzk
from(
select b.C_STORE_ID ,count(a.id) as lsds,sum(a.qty)as lsjs,sum(a.TOT_AMT_LIST)as dpje,sum(a.TOT_AMT_ACTUAL) as lsje,
case when SUM(a.TOT_AMT_LIST)=0 then null else round(sum(a.TOT_AMT_ACTUAL)/SUM(a.TOT_AMT_LIST),2)end as pjzk
from M_RETAILITEM a
left join M_RETAIL b on a.M_RETAIL_ID=b.id
left join M_PRODUCT c on a.M_PRODUCT_ID=c.id
where (c.M_DIM4_ID not in (861,862) or a.TOT_AMT_ACTUAL<>0) and a.ISACTIVE='Y' and A.STATUS='2'
and b.billdate=to_char(to_date('2020-05-22','YYYY-MM-DD'),'YYYYMMDD')
GROUP BY b.C_STORE_ID
)a
) d on a.id=d.C_STORE_ID
ORDER BY nvl(d.lsje,0) desc) where rownum<21
所有回答(2)
0
大兄弟你这个脚本有点复杂啊，想简单点分组无非为了聚合嘛。
对的就是聚合函数 昨天紧急 把最简单得给忘了，被师傅叼了一天，
0
在中间那段修改双层循环
select a.C_STORE_ID,a.sl,a.dpje,a.cjje,a.pjzk
from(
select b.C_STORE_ID,sum(a.qty)as sl,sum(a.TOT_AMT_LIST)as dpje,sum(a.TOT_AMT_ACTUAL) as cjje,
case when SUM(a.TOT_AMT_LIST)=0 then null else round(sum(a.TOT_AMT_ACTUAL)/SUM(a.TOT_AMT_LIST),2)end as pjzk
from M_RETAILITEM a
left join M_RETAIL b on a.M_RETAIL_ID=b.id
left join M_PRODUCT c on a.M_PRODUCT_ID=c.id
where (c.M_DIM4_ID not in (861,862) or a.TOT_AMT_ACTUAL<>0) and a.ISACTIVE='Y' and A.STATUS='2'
and b.billdate=to_char(to_date('${日期}','YYYY-MM-DD'),'YYYYMMDD')
GROUP BY b.C_STORE_ID