sql server 2005 select update 复杂计算 批量更新
发布网友
发布时间：2022-04-09 01:20
共1个回答
热心网友
时间：2022-04-09 02:50
--建表
Create Table T
(
id int,
riqi datetime,
daima Varchar(10),
kaipan numeric(18,2),
zuigao numeric(18,2),
zuidi numeric(18,2),
zhenfu numeric(18,6)
)
--插入数据
insert into t values(1,'2015-10-1','600000',1,7,1,null)
insert into t values(2,'2015-10-2','600000',2,8,2,null)
insert into t values(3,'2015-10-3','600000',3,9,3,null)
insert into t values(4,'2015-10-4','600000',4,10,4,null)
insert into t values(5,'2015-10-5','600000',5,11,5,null)
insert into t values(6,'2015-10-6','600000',6,12,6,null)
insert into t values(7,'2015-10-1','700000',5,13,7,null)
insert into t values(8,'2015-10-2','700000',4,14,8,null)
insert into t values(9,'2015-10-3','700000',3,15,7,null)
insert into t values(10,'2015-10-4','700000',2,16,8,null)
--更新
--思路:按daima分组riqi排序产生记录号id1,当id1<=3时取null,否则取出id1-3到id1-1记录zuida的最大值减去zuixiao的最小值,然后除以id1 = A.id1-3对应的kaipan,最后把计算结果更新给zhenfu
With CT
AS
(
select *,ROW_NUMBER() over(partition by daima order by riqi) As id1 from t
)
Update T Set zhenfu=B.Rst
From (
Select *,Case When id1<=3 then null else
Round(
(
(select max(zuigao) From CT Where daima=A.daima And id1 between A.id1-3 and A.id1-1)-
(select min(zuidi) From CT Where daima=A.daima And id1 between A.id1-3 and A.id1-1)
)/(select kaipan From CT Where daima=A.daima And id1 = A.id1-3),5)
End As Rst
From CT A
) B Where T.id=B.id
/*
计算公式
id=4 Select (9-1)/1
id=5 Select (10-2)/2
id=6 Select (11-3)/3.0
id=10 select (15-7)/5.0
其他id的为null
*/
--查看结果
Select * from T
