//ID FType FNumber FYield FCost FTotalCost FNote //1 A 1 .99 98 FTotalCost期望结果:98 //2 A 2 .98 200 FTotalCost期望结果:98/0.98+200=300 //3 A 3 .97 300 FTotalCost期望结果:300/0.97+300=609.28 //4 A 4 .96 400 FTotalCost期望结果:609.28/0.96+400=1034.67 //5 B 1 .99 100 FTotalCost期望结果:100 //6 B 2 .98 200 FTotalCost期望结果:100/0.98+200=302.04 //7 B 3 .97 300 FTotalCost期望结果:302.04/0.97+300=611.38 //8 C 1 .99 100 FTotalCost期望结果:100 //9 C 2 .98 200 FTotalCost期望结果:100/0.98+200=302.04
FNumber最大不大的话,并假设FNumber从1开始没有跳号,可递归cte;with cte as( select ID,FType,FNumber,FYield,FCost,FCost as FTotalCost,FNote from t_test where FNumber = 1 union all select a.ID,a.FType,a.FNumber,a.FYield,a.FCost,b.FTotalCost / a.FYield + a.FCost as FTotalCost,a.FNote from t_test a,cte b where a.FNumber = b.FNumber + 1 and a.FType = b.FType ) select * from cte
图片好像不能显示:
请移步去我相册看:
http://hi.csdn.net/space-1186507-do-album-picid-940953.html
//ID FType FNumber FYield FCost FTotalCost FNote
//1 A 1 .99 98 FTotalCost期望结果:98
//2 A 2 .98 200 FTotalCost期望结果:98/0.98+200=300
//3 A 3 .97 300 FTotalCost期望结果:300/0.97+300=609.28
//4 A 4 .96 400 FTotalCost期望结果:609.28/0.96+400=1034.67
//5 B 1 .99 100 FTotalCost期望结果:100
//6 B 2 .98 200 FTotalCost期望结果:100/0.98+200=302.04
//7 B 3 .97 300 FTotalCost期望结果:302.04/0.97+300=611.38
//8 C 1 .99 100 FTotalCost期望结果:100
//9 C 2 .98 200 FTotalCost期望结果:100/0.98+200=302.04
select ID,FType,FNumber,FYield,FCost,FCost as FTotalCost,FNote
from t_test
where FNumber = 1
union all
select a.ID,a.FType,a.FNumber,a.FYield,a.FCost,b.FTotalCost / a.FYield + a.FCost as FTotalCost,a.FNote
from t_test a,cte b
where a.FNumber = b.FNumber + 1 and a.FType = b.FType
)
select * from cte
您好,FNumber最多15,中间不会有跳号,我看看您的这个方法
这个放在ACCESS里运行报错:无效的SQL语句;期待‘insert’'update''select'‘producure’
我把最前面的分号去掉了也不行,请问是不是我操作错了?
你这个需要递归,用access的话最好在程序计算了,数据库难以实现硬放在数据库实现的话性能就保证不了了
http://topic.csdn.net/u/20110801/17/d5a238bb-e82d-4059-8fc1-a705cd0fd0ff.html