求助一个SQL

显示全部楼层 · 2008-8-14 11:45:20

table income
idvalue
-----------
2055
21100
22150
要求比较特殊，对于给定数值，比如80，要求第三列小于等于value，且第三列和为80，如：
idvaluecol
----------------
2055 55
21100 25
22150 0
请问各位TX，这个SQL怎么写？

千问 · 2008-8-14 11:45:20

补充，如果是200，则如下：
idvaluecol
----------------
2055 55
21100 100
22150 45

如果是400，则如下
idvaluecol
----------------
2055 55
21100 100
22150 155

千问 · 2008-8-14 11:45:20

假设最大400
SQL> with income as(select 20 id,55 value from dual
2union all select 21 id,100 value from dual
3union all select 22 id,150 value from dual
4union all select 23 id,110 value from dual
5union all select 24 id,140 value from dual)
6select * from income;
IDVALUE
---------- ----------
20 55
21100
22150
23110
24140

SQL> with income as(select 20 id,55 value from dual
2union all select 21 id,100 value from dual
3union all select 22 id,150 value from dual
4union all select 23 id,110 value from dual
5union all select 24 id,140 value from dual)
6select id,value,sum(value) over(order by id) sum,
7case when sum(value) over(order by id)=0
10
then value-(sum(value) over(order by id)-400)
11 else 0 end
12end col
13 from income;
IDVALUESUMCOL
---------- ---------- ---------- ----------
20 55 55 55
21100155100
22150305150
23110415 95
24140555
0
SQL>
[ 本帖最后由 kmpx 于 2008-9-24 16:57 编辑 ]

千问 · 2008-8-14 11:45:20

select id,value,
(case when val <= &n then value

when val - value <= &n then &n - (val - value)

else 0 end) col
from (select id,value,sum(value)over(order by id) val from income);

千问 · 2008-8-14 11:45:20

kmpx 动作挺快嘛.

千问 · 2008-8-14 11:45:20

千问 · 2008-8-14 11:45:20

SQL写短点, 改进一下:
select id,value,greatest(least(value,80 - (val - value)),0) col
from (select id,value,sum(value)over(order by id) val from income);

千问 · 2008-8-14 11:45:20

select id, value, greatest(least(value, 200 - (sum(value) over(order by id) - value)), 0) col
from income;

千问 · 2008-8-14 11:45:20

with income as(select 20 id,55 value from dual
union select 21 id,100 value from dual
union select 22 id,150 value from dual
union select 23 id,110 value from dual
union select 24 id,140 value from dual)
select id,value,decode(sign(val-&n),1,(val-value),value) col
from (select id,value,sum(value)over(order by id) val from income);

千问 · 2008-8-14 11:45:20

根据楼主的要求：
如果是400，则如下
idvaluecol
----------------
2055 55
21100 100
22150 155
好像还要判断一下是否是最后一行，是的话有可能最后一行的col会大于value
select id,value,val,
decode(a.arn,b.brn,(case when val <= &n then &n-(val-value)

when val - value <= &n then &n - (val - value)

else 0 end) ,

(case when val <= &n then value

when val - value <= &n then &n - (val - value)

else 0 end)

)col
from (select rownum arn ,id,value,sum(value)over(order by id) val from income) a,
(select count(*) brn from income) b;