oracle按列求乘積(轉)

ID QTY
1 2
2 4
3 6
4 5
要求能夠得到QTY欄位的乘積2*4*6*5,

運用一下數學知識， 可以這樣簡化：

a * b * c = power(10, log(10, a) + log(10, b) + log(10, c)

Select power(10, Sum(Log(10, qty))) From t



scott@O9I.US.ORACLE.COM> drop table t;

Table dropped.

scott@O9I.US.ORACLE.COM> create table t (id number, qty number);

Table created.

scott@O9I.US.ORACLE.COM> insert into t values(1, 2);

1 row created.

scott@O9I.US.ORACLE.COM> insert into t values(2, 4);

1 row created.

scott@O9I.US.ORACLE.COM> insert into t values(3, 6);

1 row created.

scott@O9I.US.ORACLE.COM> insert into t values(4, 5);

1 row created.

scott@O9I.US.ORACLE.COM> commit;

Commit complete.

scott@O9I.US.ORACLE.COM> Select power(10, Sum(Log(10, qty))) From t
2 ;

POWER(10,SUM(LOG(10,QTY)))
--------------------------
240

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242356/，如需轉載，請註明出處，否則將追究法律責任。