22,206
社区成员
发帖
与我相关
我的任务
分享
SELECT ID,A, exp(sum(log(A*0.01*0.01))/0.01/0.01 OVER(ORDER BY ID DESC) B
FROM T
WITH ROWCTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY ID) RN,* FROM 你的表
)
,CTE AS(
SELECT TOP 1 *,CAST(A AS DECIMAL(19,6)) B FROM ROWCTE ORDER BY ID DESC
UNION ALL
SELECT T1.*,CAST(T2.B*0.01*T1.A*0.01 AS DECIMAL(19,6)) FROM ROWCTE T1 INNER JOIN CTE T2 ON T1.RN+1=T2.RN
)
SELECT ID,A,B FROM CTE ORDER BY ID
--按照A分组,按照B排序,累乘C
select A,B,
exp(sum(log(C)) over(partition by A order by B)) RESULT
from T
WITH CTE AS(
SELECT TOP 1 *,CAST(A AS DECIMAL(19,6)) B FROM 你的表 ORDER BY ID DESC
UNION ALL
SELECT T1.*,CAST(T2.B*0.01*T1.A*0.01 AS DECIMAL(19,6)) FROM 你的表 T1 INNER JOIN CTE T2 ON T1.ID+1=T2.ID
)
SELECT * FROM CTE ORDER BY ID
不知道你的意思是不是这样,ID是递增为1吗?DECIMAL(19,6)换成你要的精确度