update如何更新同一表中多条记录

[iloveyoubaby]

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

我有一表a,结构如上,主键为:(ssdh,qydh,cpdm,InputYearMonth).我在存储过程中，根据InputYearMonth=200706插入两条记录，并将InputYearMonth设置为200707（也就是复制上月记录，并将InputYearMonth设定为当前年月），并将jdj设定为上个月相应记录的(bdj1+bdj2)/2。现在问题是：如何将200707相应记录的tdj设置为200607相应记录的（bdj1+bdj2）/2

我插入记录是用下面这条语句：
'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

插入200707月记录后，要将新插入记录的tdj字段更新为200607月相应记录的（bdj1+bdj2）/2

想了老半天了，还没有很好解决这个问题

[iloveyoubaby]

[Quote=引用 15 楼 Garnett_KG 的回复:]
SQL code

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=200606
AND ssdh =b.ssdh AND qrydh=b.qrydh
) as tdj

from a as b
where InputYearMonth='200706' and ssdh='38' and qydh='1'
[/Quote]

受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'

问题解决，谢谢大家

[iloveyoubaby]

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

[xabcxyz]

没明白问题,帮你顶一下吧

[lgxyz]

没明白
看的乱乱的

[一品梅]

在sql中写个触发器

[Garnett_KG]

没看明白.

你的这语句改一下不就可以了吗？

'insert into a select ssdh, qydh, cpdm, 0 as bdj1, 0 as bdj2, (bdj1+bdj2)/2 as jdj,
(bdj1+bdj2)/2 as tdj, 200707 as InputYearMonth from a where InputYearMonth=200706 and ssdh='+@ssdh+' and qydh='+@qydh

[昵称被占用了]

200707的bdj1，bdj2不都是0吗？那设置tdj是0是对的

[ybkenan]

樓主把我的思維搞亂了， update where 條件
這條件沒明白你的意思，所以不知道怎麼寫。

[lxuan_025]

可以写一个函数。

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)型的

[iloveyoubaby]

[Quote=引用 1 楼 iloveyoubaby 的回复:]
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
[/Quote]

对不起。可能没讲清楚
我这个存储过程分两步：
第一步：假如本月为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来更新记录了

[iloveyoubaby]

我这个第二步，是要一次更新多条记录的

[iloveyoubaby]

第二步更新的是tdj,而第一步是jdj，两次字段不同

[lxuan_025]

你自己写的语句的字段对应有问题，改过来也就行了

insert into a select ssdh, qydh, cpdm,  '200707' as InputYearMonth ,0 as bdj1, 0 as bdj2, (bdj1+bdj2)/2as jdj, 0 as tdj
from a 
where InputYearMonth='200706' and ssdh='38' and qydh='1'

[水族杰纶]

看看先~~~~~~~~~

[chenjunsheep]

坐下慢慢看...

[Garnett_KG]

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=200606 
           AND ssdh =b.ssdh AND qrydh=b.qrydh
     ) as tdj

from a  as b
where InputYearMonth='200706' and ssdh='38' and qydh='1'

[lxuan_025]

这样呢？

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'

[-晴天]

38 1 44200101 200607 1 1 1.1 0.8 
200607相应记录的（bdj1+bdj2）/2 =1
你怎么得出1.1来的?

[-晴天]

为什么要插入后再更新呢?如果你插入的那两条记录都要更新,那为什么不直接在插入时就用 200607 的数据来运算?