数据如下：
类别 品名 数量
A 鸭脖 25.5
A 鸡爪 82
B 脉动 26
A 香肠 12
B 加多宝 22
需求是我要得到A类中每个品名占自己类别数量的百分比SQL该如何写？
想查询得到
品名 数量 比例
鸭脖 25.5 21.34%
鸡爪 82 68.62%
香肠 12 10.04%
脉动 26 54.17%
加多宝 22 45.83%
------解决方案--------------------
sql写成这样了 如果非要小类大类统计 考虑分开统计了union all合并吧
- SQL code
with t1 as( select 'C' c1,'A' c2,'鸭脖' c3,25.5 c4 from dual union all select 'C' c1,'A' c2,'鸡爪' c3,82 c4 from dual union all select 'C' c1,'B' c2,'脉动' c3,26 c4 from dual union all select 'C' c1,'A' c2,'香肠' c3,12 c4 from dual union all select 'C' c1,'B' c2,'加多宝' c3,22 c4 from dual union all select 'D' c1,'E' c2,'牙刷' c3,36 c4 from dual union all select 'D' c1,'E' c2,'毛巾' c3,22 c4 from dual )select c1,c2,c3,c4, round(c4/sum(c4) over(partition by c1,c2),4)*100||'%' c5, round(c4/sum(c4) over(partition by c1),4)*100||'%' c6from t1 c1 c2 c3 c4 c5 c6-----------------------1 C A 鸭脖 25.5 21.34% 15.22%2 C A 鸡爪 82 68.62% 48.96%3 C A 香肠 12 10.04% 7.16%4 C B 加多宝 22 45.83% 13.13%5 C B 脉动 26 54.17% 15.52%6 D E 毛巾 22 37.93% 37.93%7 D E 牙刷 36 62.07% 62.07%