ssdh | qydh | cpdm | InputYearMonth | bdj1 | bdj1 | 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 |
我有一表a,结构如上,主键为:(ssdh,qydh,cpdm,InputYearMonth).
我在存储过程中,根据InputYearMonth=200706插入两条记录,并将InputYearMonth设置为200707(也就是复制上月记录,并将InputYearMonth设定为当前年月),并将jdj设定为上个月相应记录的(bdj1+bdj2)/2。现在问题是:如何将200707相应记录的tdj设置为200607相应记录的(bdj1+bdj2)/2
假如本月为200707月,那么我新插入一些记录,这些记录是根据上个月的记录生成的。比如倒数第二条记录
是根据倒数第4条记录生成。只是插入倒数第二条记录时,将InputYearMonth设定为当前月份,将jdj设定为倒数第四条记录bdj1和bdj2的算术平均值。
新插入记录的tdj是根据去年同期相应记录的bdj1和bdj2的算术平均值来确定的。比如倒数第二条记录(200707月)的tdj字段的值要由第一条记录(200607月)的bdj1和bdj2的算术平均值来确定。
我是如下实现的,能同时插入多条记录:
- 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'
同时跟新多条记录:
- update a
- set tdj=(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 )
- from a as b
- where InputYearMonth='200707' and ssdh='4303' and qydh='0002'
本问题讨论贴:
http://topic.csdn.net/u/20080909/10/d8e340ef-3656-478b-a4cb-1ba8bc821b49.html?seed=672223447