ssdh qydh cpdm InputYearMonth bdj1 bdj2 jdj tdj
38 1 66100202 200607 100 100 120 110
38 1 44200101 200607 1 1 1.1 0.8
38 1 66100202 200706 108 108 110 110
38 1 44200101 200706 1.1 1.1 1.2 1.2
38 1 66100202 200707 0 0 108 0
38 1 44200101 200707 0 0 1.1 0
38 1 66100202 200607 100 100 120 110
38 1 44200101 200607 1 1 1.1 0.8
38 1 66100202 200706 108 108 110 110
38 1 44200101 200706 1.1 1.1 1.2 1.2
38 1 66100202 200707 0 0 108 0
38 1 44200101 200707 0 0 1.1 0
(bdj1+bdj2)/2 as tdj, 200707 as InputYearMonth from a where InputYearMonth=200706 and ssdh='+@ssdh+' and qydh='+@qydh
這條件沒明白你的意思,所以不知道怎麼寫。
可以写一个函数。create function fsum(@bdj1 float ,@bdj2 float)
returns float
as begin
declare @jdj float
set @jdj = (@bdj1+@bdj2)/2
return @jdj
end -- 函数insert into a select ssdh, qydh, cpdm, '200707' as InputYearMonth ,0 as bdj1, 0 as bdj2, dbo.fsum(bdj1,bdj2)as jdj, 0 as tdj
from a
where InputYearMonth='200706' and ssdh='38' and qydh='1' -- 我用的日期为varchar(20)型的
对不起。可能没讲清楚
我这个存储过程分两步:
第一步:假如本月为200707月,那么我新插入一些记录,这些记录是根据上个月的记录生成的。比如倒数第二条记录
是根据倒数第4条记录生成。只是插入倒数第二条记录时,将InputYearMonth设定为当前月份,将jdj设定为倒数第四条记录bdj1和bdj2的算术平均值。
这就是这条语句的作用
'insert into a select ssdh, qydh, cpdm, 0 as bdj1, 0 as bdj2, (bdj1+bdj2)/2 as jdj, 0 as tdj, 200707 as InputYearMonth from a where InputYearMonth=200706 and ssdh='+@ssdh+' and qydh='+@qydh 第二步:更新新插入记录的tdj字段值
新插入记录的tdj是根据去年同期相应记录的bdj1和bdj2的算术平均值来确定的。比如倒数第二条记录(200707月)的tdj字段的值要由第一条记录(200607月)的bdj1和bdj2的算术平均值来确定。
由于在第一步中已经插入了记录,所以第二步只能用update来更新记录了
from a
where InputYearMonth='200706' and ssdh='38' and qydh='1'
select ssdh, qydh, cpdm, '200707' as InputYearMonth ,0 as bdj1, 0 as bdj2,
(bdj1+bdj2)/2as jdj, (
SELECT TOP 1 (bdj1+bdj2)/2
FROM a
WHERE InputYearMonth=200606
AND ssdh =b.ssdh AND qrydh=b.qrydh
) as tdjfrom a as b
where InputYearMonth='200706' and ssdh='38' and qydh='1'
insert into a select ssdh, qydh, cpdm, '200707' as InputYearMonth ,0 as bdj1, 0 as bdj2, (bdj1+bdj2)/2as jdj, (select (bdj1+bdj2)/2 as tdj from a where InputYearMonth = '200607') as tdj
from a
where InputYearMonth='200706' and ssdh='38' and qydh='1'
200607相应记录的(bdj1+bdj2)/2 =1
你怎么得出1.1来的?
受15楼兄弟启发,编写如下代码 insert into a
select ssdh, qydh, cpdm, '200707' as InputYearMonth ,0 as bdj1, 0 as bdj2,
(bdj1+bdj2)/2as jdj, (
select top 1 (bdj1+bdj2)/2
from a
where InputYearMonth=convert(char(4),convert(int,left('200707',4))-1)+right('200707',2)
and ssdh=b.ssdh and qydh=b.qydh and cpdm=b.cpdm
) as tdj from a as b
where InputYearMonth='200706' and ssdh='38' and qydh='1'
问题解决,谢谢大家