首页 新闻 搜索 专区 学院

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

借口/*-的主页 借口/*- | 初学一级 | 园豆:61
提问于:2021-07-29 10:24
< >
分享
所有回答(2)
0

大兄弟你这个脚本有点复杂啊,想简单点分组无非为了聚合嘛。

KingMi | 园豆:1266 (小虾三级) | 2021-07-30 11:25

对的就是聚合函数 昨天紧急 把最简单得给忘了,被师傅叼了一天,

支持(0) 反对(0) 借口/*- | 园豆:61 (初学一级) | 2021-07-30 11:27
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
借口/*- | 园豆:61 (初学一级) | 2021-07-30 11:30
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册