求助,如何才能在原表的情况下,查询得到结果表的效果。将每人三餐的合计数计算出来,然后按三餐的占比,列出。同时,对低占比的,舍去忽略。 name zl jine 张三 早餐A 5 李四 早餐A 5 张三 早餐B 20 张三 午餐 30 张三 晚餐 40 李四 晚餐 30 希望得到的结果(占比低于20%
求助,如何通过查询原表得到结果表的结果?算一下每人三餐的总数,然后按照三餐的比例列出来。同时,比例低的应该忽略。
name
zl
jine
张三
早餐A
5
李四
早餐A
5
张三
早餐B
20
张三
午餐
30
张三
晚餐
40
李四
晚餐
30
希望得到的结果(占比低于20%的不显示)
name
三餐费用
jieguo
张三
95
晚餐42.10%午餐31.58%
李四
35
晚餐85.71%
opal
Oracle 12C
with d1 as(
select '张三' as name, '早餐A' as zl, 5 as jine from dual
union all
select '李四' as name, '早餐A' as zl, 5 as jine from dual
union all
select '张三' as name, '早餐B' as zl, 20 as jine from dual
union all
select '张三' as name, '午餐' as zl, 30 as jine from dual
union all
select '张三' as name, '晚餐' as zl, 40 as jine from dual
union all
select '李四' as name, '晚餐' as zl, 30 as jine from dual
), d2 as(
select name, sum(jine) as zj from d1 group by name
), d3 as(
select d1.name, d1.zl, d1.jine, d2.zj,
trunc(d1.jine / d2.zj * 100,2) xj
from d1, d2
where d1.name = d2.name
)
select name, zj, listagg(zl||xj||'%',',') within group (order by zl) as jieguo
from d3
where xj >= 20
group by name, zj
pargy
前天 09:03
谢谢您!谢谢您,老师,谢谢给予的帮助!分步实现,这个思路太棒了
浅浅的无名小卒
原表查询不知道,但是用存储过程肯定是可以的,查看的时候调用存储过程就行了
pargy
前天 09:03
谢谢您,谢谢!