有两个表,表A
A1 A2
A0001 980
A0002 752
A0003 547
A0004 856
表B
A1 B1 B2 B3
A0001 26111 39.50 39.30
A0001 26109 57.76 57.86
A0001 26088 63.28 63.74
A0002 26079 52.42 52.30
A0002 26071 59.06 58.76
A0002 26069 56.66 56.38
A0003 26162 38.20 38.08
A0003 26158 39.02 38.86
A0003 26155 38.80 38.68
A0003 26147 37.36 37.18
A0003 26146 40.70 40.36
A0004 26250 58.34 57.76
当B3-B2<=B3*3/1000时,按照A1进行分组求和费用=SUM(A2*B3),否则按照A1进行分组求和费用=SUM((A2-(B3*3/1000-B2)*A2)*B3),这个sql语句在Oracle应该怎样实现,谢谢?Oracle分组 求和
A1 A2
A0001 980
A0002 752
A0003 547
A0004 856
表B
A1 B1 B2 B3
A0001 26111 39.50 39.30
A0001 26109 57.76 57.86
A0001 26088 63.28 63.74
A0002 26079 52.42 52.30
A0002 26071 59.06 58.76
A0002 26069 56.66 56.38
A0003 26162 38.20 38.08
A0003 26158 39.02 38.86
A0003 26155 38.80 38.68
A0003 26147 37.36 37.18
A0003 26146 40.70 40.36
A0004 26250 58.34 57.76
当B3-B2<=B3*3/1000时,按照A1进行分组求和费用=SUM(A2*B3),否则按照A1进行分组求和费用=SUM((A2-(B3*3/1000-B2)*A2)*B3),这个sql语句在Oracle应该怎样实现,谢谢?Oracle分组 求和
case
when sum(B3) - sum(B2) <= sum(B3) * 3 / 1000 then
SUM(A2 * B3)
else
SUM((A2 - (B3 * 3 / 1000 - B2) * A2) * B3)
end x
FROM B
join a
on b.a1 = a.a1
group by b.a1;
158055.603832
74936.351808
73797.722134
12198.468403只是这个费用根据条件判断,获取结果不一样
sum(case
when b.B3 - b.B2 <= b.B3 * 3 / 1000 then
a.A2 * b.B3
else
(a.A2 - (b.B3 * 3 / 1000 - b.B2) * a.A2) * b.B3
end)
from a, b
where a.a1 = b.a1
group by b.a1