如记录 表A
a b
1 10
2 11
3 14
计算结果:
1 10 10/(10+11+14)
2 11 11/(10+11+14)
3 14 14/(10+11+14)
注意要求:
不能使用 一下类似语句,
select a,b,b/(select sum(b) from A)
from A
目标SQL中,只能有一个A
a b
1 10
2 11
3 14
计算结果:
1 10 10/(10+11+14)
2 11 11/(10+11+14)
3 14 14/(10+11+14)
注意要求:
不能使用 一下类似语句,
select a,b,b/(select sum(b) from A)
from A
目标SQL中,只能有一个A
SQL>
SQL> with A as
2 (
3 select 1 a, 10 b
4 from dual
5 union all
6 select 2, 11
7 from dual
8 union all
9 select 3, 14 from dual
10 )
11 select a, b, trunc(a/c,2) from (select a, b, sum(b)over(order by 1) c from a)
12 ; A B TRUNC(A/C,2)
---------- ---------- ------------
1 10 0.02
2 11 0.05
3 14 0.08SQL>
SELECT 1 a,10 b FROM dual UNION ALL
SELECT 2, 11 FROM dual UNION ALL
SELECT 3, 14 FROM dual
)
select a,b,round(b/sum(b)over()*100,2)||'%' 百分比 from A--结果
A B 百分比
--------------------
1 10 28.57%
2 11 31.43%
3 14 40%
如果不写就会因为没有group by 报错
------4蛇5乳
SQL> with A as
2 (
3 select 1 a, 10 b
4 from dual
5 union all
6 select 2, 11
7 from dual
8 union all
9 select 3, 14 from dual
10 )
11 select a, b, round(a/c,2) from (select a, b, sum(b)over(order by 1) c from a)
12 ; A B ROUND(A/C,2)
---------- ---------- ------------
1 10 0.03
2 11 0.06
3 14 0.09SQL>
2 (select 1 a,10 b from dual union all
3 select 2,11 from dual union all
4 select 3,14 from dual )
5 select a,b,b/sum(b) over() from tb
6 /
A B B/SUM(B)OVER()
---------- ---------- --------------
1 10 0.285714285714
2 11 0.314285714285
3 14 0.4