SQL 更新查询

Question

我有一个主表（例如主表）和另一个临时表（例如副本）。 我想做的是主表中每个存储过程的平均值和标准差（其中有一堆具有不同版本号作为后缀，并且它们都必须被相同地对待），并更新临时表，其中每个存储过程的平均值和标准差天。 以下是我的不起作用的代码......

UPDATE @CopyOfMainTable AS copy
            SET copy.overall_count_average = AVG(main.overall_count),
                        copy.overall_count_stddev=StDev(main.overall_count),
                        copy.redundancy_count_average =AVG (main.redundancy_count),
                        copy.redundancy_count_stddev=StDev(main.redundancy_count),
                        copy.not_completed_count_average AVG(main.not_completed_count),
                        copy.not_completed_count_stddev=StDev(main.not_completed_count),
                        copy.session_count_average= AVG(main.session_count),
                        copy.session_count_stddev=StDev(main.session_count),
                        copy.per_session_count_average_average = AVG (main.per_session_count_avg),
                        copy.per_session_count_average_stddev = StDev(main.per_session_count_avg),
                        copy.per_session_count_max_average = AVG(main.per_session_count_max),
                        copy.per_session_count_max_stddev= StDev(main.per_session_count_max),
                        copy.per_session_count_stddev_average=AVG(main.per_session_count_stddev),
                        copy.per_session_count_stddev_stddev=StDev(main.per_session_count_stddev),
                        copy.run_time_average_average =  AVG(main.run_time_avg),
                        copy.run_time_average_stddev = StDev(main.run_time_avg),
                        copy.run_time_max_average =AVG(main.run_time_max),
                        copy.run_time_max_stddev= StDev(main.run_time_max),
                        copy.run_time_stddev_average=AVG(main.run_time_stddev),
                        copy.run_time_stddev_stddev=StDev(main.run_time_stddev),
                        copy.run_time_core_hours_avg_average=Avg(main.run_time_core_hours_avg),
                        copy.run_time_core_hours_avg_stddev=StDev(main.run_time_core_hours_avg),
                        copy.run_time_core_hours_max_average=Avg(main.run_time_core_hours_max),
                        copy.run_time_core_hours_max_stddev=StDev(main.run_time_core_hours_max),
                        copy.run_time_core_hours_stddev_average=Avg(main.run_time_core_hours_stddev),
                        copy.run_time_core_hours_stddev_stddev=StDev(main.run_time_core_hours_stddev),
                        copy.run_time_peak_hours_avg_average=Avg(main.run_time_peak_hours_avg),
                        copy.run_time_peak_hours_avg_stddev=StDev(main.run_time_peak_hours_avg),
                        copy.run_time_peak_hours_max_average=Avg(main.run_time_peak_hours_max),
                        copy.run_time_peak_hours_max_stddev=StDev(main.run_time_peak_hours_max),
                        copy.run_time_peak_hours_stddev_average=Avg(main.run_time_peak_hours_stddev),
                        copy.run_time_peak_hours_stddev_stddev=StDev(main.run_time_peak_hours_stddev),
                        copy.run_time_min_average=Avg(main.run_time_min),
                        copy.run_time_min_stddev=StDev(main.run_time_min),
                        copy.run_time_core_hours_min_average=Avg(main.run_time_core_hours_min),
                        copy.run_time_core_hours_min_stddev=StDev(main.run_time_core_hours_min),
                        copy.run_time_peak_hours_min_average=Avg(main.run_time_peak_hours_min),
                        copy.run_time_peak_hours_min_stddev=StDev(main.run_time_peak_hours_min)
            FROM dbo.database_call_tracking as main 
            WHERE(main.date < @latestDay_ToBeConsidered_Forthreshhold) AND (main.day_of_week BETWEEN 2 AND 6)  AND (main.database_call like '%'+copy.database_call+'%')--AND (main.database_call LIKE '%[_]v[1-9]%'
            Group by database_call

这根本不起作用。 有人可以帮忙吗？ 或者可能会建议什么？

Answer 1

通常，您需要针对此场景使用子查询； 这是一个简化示例：

UPDATE #tmp
SET #tmp.SomeValue = x.SomeValue
FROM @MyTempTable #tmp
INNER JOIN (
    SELECT blah.SomeKey, SUM(blah.Whatever) as [SomeValue]
    /* some complex query */
    GROUP BY blah.SomeKey
) x ON #tmp.SomeKey = x.SomeKey