将成功的SQL Server Select with JOINS改为Update

Question

我有一个日志文件tblktime，其中包含'Ourfile'的所有活动。创建了第二个汇总表tblktimefst(每个ourfile一条记录)，用于捕获日志文件中特定代码的首次出现，并总结该ourfile的第一条记录与它出现在tblktime日志中的时间之间的"duration"列。(第一次出现的LSF被存储为fstLSFBtime)

我写的Select语句准确地返回唯一的id字段'Ourfile'和正确的总和'TotDur'，我想更新回tblktimefst.fstLSFDurSum

SELECT DISTINCT
ktf.ourfile,
SUM(CASE WHEN kt.btime <= ktf.fstlsfbtime THEN kt.duration ELSE 0 END) AS TotDur
FROM
tblktime kt
INNER JOIN 
tblktimefst ktf ON ktf.ourfile = kt.ourfile 
AND kt.btime <= ktf.fstLSFBtime
GROUP BY
ktf.ourfile

SQL Server返回:

TotDur190

Answer 1

(未测试):

UPDATE tblktimefst
SET fstLSFDurSum = TotDur
FROM tblktime t2
INNER JOIN
(
select distinct
ktf.ourfile,
SUM( CAse when kt.btime <= ktf.fstlsfbtime then kt.duration else 0 end) as TotDur
from tblktime kt
Inner JOIN tblktimefst ktf on ktf.ourfile = kt.ourfile and kt.btime <= ktf.fstLSFBtime
group by ktf.ourfile) x ON x.ourfile = t2.ourfile