确定就这几个值?笨办法: SELECT SUM(DECODE(NAME,'a',VALUE,0))/SUM(DECODE(NAME,'b',VALUE,0)) + SUM(DECODE(NAME,'b',VALUE,0))/SUM(DECODE(NAME,'c',VALUE,0)) FROM T
with temp as( select 'a' name,1.2 value from dual union all select 'b' name,2.5 value from dual union all select 'c' name,1.8 value from dual ) select max(decode(name,'a',value))/max(decode(name,'b',value)) - max(decode(name,'b',value))/max(decode(name,'c',value)) from temp
如果是这样的话,就好办了 --> 测试数据: #tb if object_id('tb') is not null drop table tb go create table tb (name varchar(1),value numeric(2,1)) insert into tb select 'a',1.2 union all select 'b',2.5 union all select 'c',1.8 alter table tb add id int identity(1,1)select sum(a.value/b.value) from tb a,tb b where a.id+1=b.id------------------------- 1.868888(1 行受影响)
唉,理解错了,你们。如果是在条件里算这个呢,比如 现在要求 where(a/b)<0.5的行,怎么写sql。比如 select * from t where where(a/b)<0.5
select * from t where decode(name,'a',value)/decode(name,'b',value)<0.5
select sum(t1.value / t2.value) from (select rownum rowno, value from t) t1, (select rownum rowno, bbb from (select rownum rowno, value from t) t0 where t0.rowno > 1) t2 where t1.rowno = t2.rowno
上面的语句有个错误,我是用自己库里一个有两列值(aaa,bbb)的表测试完之后改的,一时疏忽,有个'bbb' 忘记改成 'value'了 select sum(t1.value / t2.value) from (select rownum rowno, value from t) t1, (select rownum rowno, value from (select rownum rowno, value from t) t0 where t0.rowno > 1) t2 where t1.rowno = t2.rowno
哈哈,那你用自连接,假装成很多子表就可以了。假如表名是 table(select value from table where name=a)/(select value from table where name=b)- (select value from table where name=b)/(select value from table where name=c)
SQL> select * from test;
NAME VALUE ---------- ------------ a 1.20 b 2.50 c 1.80
SQL> SQL> SELECT (SELECT SUM(VALUE) FROM TEST WHERE NAME = 'a') / 2 (SELECT SUM(VALUE) FROM TEST WHERE NAME = 'b') + 3 (SELECT SUM(VALUE) FROM TEST WHERE NAME = 'a') / 4 (SELECT SUM(VALUE) FROM TEST WHERE NAME = 'b') 5 FROM DUAL;
select tmp.value/tmp.prev+tmp.prev/tmp.next from ( select t.*,lag(t.value) over(order by t.name) as prev,lead(t.value) over(order by t.name) as next from test t where t.name = 'b') tmp
SQL codeselect tmp.value/tmp.prev+tmp.prev/tmp.next from ( select t.*,lag(t.value,1,1) over(order by t.name) as prev,lead(t.value,1,1) over(order by t.name) as next from test t where t.name = 'b' ) tmp
SELECT SUM(DECODE(NAME,'a',VALUE,0))/SUM(DECODE(NAME,'b',VALUE,0))
+ SUM(DECODE(NAME,'b',VALUE,0))/SUM(DECODE(NAME,'c',VALUE,0))
FROM T
select 'a' name,1.2 value from dual
union all
select 'b' name,2.5 value from dual
union all
select 'c' name,1.8 value from dual
)
select max(decode(name,'a',value))/max(decode(name,'b',value))
- max(decode(name,'b',value))/max(decode(name,'c',value)) from temp
--> 测试数据: #tb
if object_id('tb') is not null drop table tb
go
create table tb (name varchar(1),value numeric(2,1))
insert into tb
select 'a',1.2 union all
select 'b',2.5 union all
select 'c',1.8 alter table tb add id int identity(1,1)select sum(a.value/b.value) from tb a,tb b where a.id+1=b.id-------------------------
1.868888(1 行受影响)
select sum(t1.value / t2.value)
from (select rownum rowno, value from t) t1,
(select rownum rowno, bbb
from (select rownum rowno, value from t) t0
where t0.rowno > 1) t2
where t1.rowno = t2.rowno
select sum(t1.value / t2.value)
from (select rownum rowno, value from t) t1,
(select rownum rowno, value
from (select rownum rowno, value from t) t0
where t0.rowno > 1) t2
where t1.rowno = t2.rowno
(select value from table where name=b)/(select value from table where name=c)
NAME VALUE
---------- ------------
a 1.20
b 2.50
c 1.80
SQL>
SQL> SELECT (SELECT SUM(VALUE) FROM TEST WHERE NAME = 'a') /
2 (SELECT SUM(VALUE) FROM TEST WHERE NAME = 'b') +
3 (SELECT SUM(VALUE) FROM TEST WHERE NAME = 'a') /
4 (SELECT SUM(VALUE) FROM TEST WHERE NAME = 'b')
5 FROM DUAL;
(SELECTSUM(VALUE)FROMTESTWHERE
------------------------------
0.96
SQL>
(
select t.*,lag(t.value) over(order by t.name) as prev,lead(t.value) over(order by t.name) as next from test t where t.name = 'b') tmp
SQL codeselect tmp.value/tmp.prev+tmp.prev/tmp.next from
(
select t.*,lag(t.value,1,1) over(order by t.name) as prev,lead(t.value,1,1) over(order by t.name) as next from test t where t.name = 'b'
) tmp