概念:

使用對數可以達到你要的

下面是注意事項跟照你需求寫的範例

注意:

exp(SUM(ln(C))) 會有浮點數問題，所以要使用floor來取整數

範例:

with temp_table as (

select 'A' A , 'A' B , 2 C from dual

union all select 'B' A , 'B' B , 2 C from dual

union all select 'B' A , 'B' B , 2 C from dual

union all select 'B' A , 'B' B , 4 C from dual

union all select 'A' A , 'A' B , 58 C from dual

)

select A,B,SUM(C) 相加,floor(exp(SUM(ln(C)))) 相乘結果,count(1) 每組數量 from temp_table

group by A,B

;

問題:

會出現這一錯誤訊息。

ORA-01428:argument '0' is out of range

原因:

因為數字裡面有0 或是 負數

解決方式:

A方法:假如不同意 0 * 1 * 2 = 0 的情況

可以加入where條件

where c > 0

剔除0跟負數問題

例子:

with temp_table as (

select 'A' A , 'A' B , 2 C from dual

union all select 'B' A , 'B' B , 2 C from dual

union all select 'B' A , 'B' B , 2 C from dual

union all select 'B' A , 'B' B , 0 C from dual

union all select 'A' A , 'A' B , 58 C from dual

)

select A,B,SUM(C) 相加,floor(exp(SUM(ln(C)))) 相乘結果,count(1) 每組數量 from temp_table

where c > 0

group by A,B

B方法:假如同意 0 * 1 * 2 = 0 的情況，我在想想怎麼寫

想出來了，負數跟0的問題都可以解決:

範例:

with temp_table as (

select 'A' A , 'A' B , 2 C from dual

union all select 'B' A , 'B' B , 2 C from dual

union all select 'B' A , 'B' B , 2 C from dual

union all select 'B' A , 'B' B , 0 C from dual

union all select 'C' A , 'C' B , -200 C from dual

union all select 'C' A , 'C' B , 4 C from dual

union all select 'A' A , 'A' B , 58 C from dual

)

, 有0的資料 as (

select * from temp_table

where C = 0

)

select A,B,ROUND(exp(sum(ln(abs(C))))*power(-1,sum(decode(sign(C),-1,1,0)))) from temp_table T100

where T100.A||T100.B not in (select A||B from 有0的資料 )

group by A,B

union all

select A,B,0

from 有0的資料