--测试数据 create table t(id int,qty int); insert into t select 1,2 from dual union all select 2,4 from dual union all select 3,6 from dual union all select 4,5 from dual; --执行查询 Select power(10, Sum(Log(10, qty))) From t --查询结果 240
楼上太牛了,数学学得太好了,我都忘光了 Select power(2, Sum(Log(2, qty))) From t; Select power(3, Sum(Log(2, qty))) From t; Select power(4, Sum(Log(4, qty))) From t; 都行.
hongqi162(失踪的月亮) ( ) 信誉:100 赞!!!我只想到了自己写一个累乘Function:)CREATE OR REPLACE FUNCTION MYSUM(sumFld VARCHAR2, sumTbl VARCHAR2) RETURN NUMBER AS FORMULA1 VARCHAR2(500); RTN_VAL NUMBER; TYPE AA IS REF CURSOR; CUR_AA AA; BEGIN OPEN CUR_AA FOR 'SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(' || sumFld || ', ''*'')),''*'') FROM ' || sumTbl || ' START WITH PREV = 0 CONNECT BY PREV = PRIOR CURR'; FETCH CUR_AA INTO FORMULA1; CLOSE CUR_AA; --DBMS_OUTPUT.PUT_LINE(FORMULA1); OPEN CUR_AA FOR 'SELECT ' || FORMULA1 || ' FROM DUAL'; FETCH CUR_AA INTO RTN_VAL; CLOSE CUR_AA; --DBMS_OUTPUT.PUT_LINE(RTN_VAL); RETURN RTN_VAL; END; /
下面是测试表及用法 CREATE TABLE KK1(NUM NUMBER, PREV NUMBER, CURR NUMBER);INSERT INTO KK1 VALUES(10, 0, 1); INSERT INTO KK1 VALUES(20,1,2); INSERT INTO KK1 VALUES(30,2,3); INSERT INTO KK1 VALUES(40,3,4);SELECT MYSUM('NUM','KK1') FROM DUAL;
在SQL中: Select power(10, Sum(Log10(qty))) From ( select id=1,qty=2 union all select 2,4 union all select 3,6 union all select 4,5 )t --结果为:239 理论上不会存在偏差, 但是在实际处理中,因为计算的精度问题. 结果出现了偏差,不知道那个高手有什么可以解决的不.
hongqi162(失踪的月亮) ( )。 真的夠牛。。贊一下嘖嘖
在SQL中: Select power(10.0, Sum(Log10(qty))) From ( select id=1,qty=2 union all select 2,4 union all select 3,6 union all select 4,5 )t --结果为:240.0 --power返回结果类型与第一参数相同.
看来大伙看问题的角度不同也是一个问题,我说一个很简单的办法 还是楼上的例子,当然注意的核心问题还是计算式超界,这个所有的方法都存在,毕竟int就那么大DECLARE @LIST VARCHAR(4096) SET @LIST='' SELECT @LIST=@LIST+'*'+CONVERT(VARCHAR,QTY) FROM TABLENAME SET @LIST=STUFF(@LIST,1,1,'') EXEC('SELECT '+@LIST) 以上算法在SQLSERVER2000下调试验通过,SYBASE下也没问题,Oracle下没试。
create table t(id int,qty int);
insert into t
select 1,2 from dual union all
select 2,4 from dual union all
select 3,6 from dual union all
select 4,5 from dual;
--执行查询
Select power(10, Sum(Log(10, qty))) From t
--查询结果
240
Select power(2, Sum(Log(2, qty))) From t;
Select power(3, Sum(Log(2, qty))) From t;
Select power(4, Sum(Log(4, qty))) From t;
都行.
赞!!!我只想到了自己写一个累乘Function:)CREATE OR REPLACE FUNCTION MYSUM(sumFld VARCHAR2, sumTbl VARCHAR2)
RETURN NUMBER
AS
FORMULA1 VARCHAR2(500);
RTN_VAL NUMBER;
TYPE AA IS REF CURSOR;
CUR_AA AA;
BEGIN
OPEN CUR_AA FOR 'SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(' || sumFld || ', ''*'')),''*'')
FROM ' || sumTbl || '
START WITH PREV = 0
CONNECT BY PREV = PRIOR CURR';
FETCH CUR_AA INTO FORMULA1;
CLOSE CUR_AA; --DBMS_OUTPUT.PUT_LINE(FORMULA1); OPEN CUR_AA FOR 'SELECT ' || FORMULA1 || ' FROM DUAL';
FETCH CUR_AA INTO RTN_VAL;
CLOSE CUR_AA; --DBMS_OUTPUT.PUT_LINE(RTN_VAL);
RETURN RTN_VAL;
END;
/
CREATE TABLE KK1(NUM NUMBER, PREV NUMBER, CURR NUMBER);INSERT INTO KK1 VALUES(10, 0, 1);
INSERT INTO KK1 VALUES(20,1,2);
INSERT INTO KK1 VALUES(30,2,3);
INSERT INTO KK1 VALUES(40,3,4);SELECT MYSUM('NUM','KK1') FROM DUAL;
啧啧~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Select power(10, Sum(Log10(qty))) From
(
select id=1,qty=2
union all select 2,4
union all select 3,6
union all select 4,5
)t
--结果为:239
理论上不会存在偏差,
但是在实际处理中,因为计算的精度问题.
结果出现了偏差,不知道那个高手有什么可以解决的不.
真的夠牛。。贊一下嘖嘖
Select power(10.0, Sum(Log10(qty))) From
(
select id=1,qty=2
union all select 2,4
union all select 3,6
union all select 4,5
)t
--结果为:240.0
--power返回结果类型与第一参数相同.
还是楼上的例子,当然注意的核心问题还是计算式超界,这个所有的方法都存在,毕竟int就那么大DECLARE @LIST VARCHAR(4096)
SET @LIST=''
SELECT @LIST=@LIST+'*'+CONVERT(VARCHAR,QTY) FROM TABLENAME
SET @LIST=STUFF(@LIST,1,1,'')
EXEC('SELECT '+@LIST)
以上算法在SQLSERVER2000下调试验通过,SYBASE下也没问题,Oracle下没试。