SQL合并AIPBalance_WeeklySnapshot表指定ID行并分组求和方法咨询

解决方案 #

第一步：更新73分组的ID和名称

执行更新语句将所有dvsAIPGroup_ID=73的记录统一修改为8号分组的属性：

UPDATE AIPBalance_WeeklySnapshot
SET dvsAIPGroup_ID = 8,
    dvsAIPGroup_Name = N'DOM'
WHERE dvsAIPGroup_ID = 73
AND AsOfDate >= '2020-06-05' -- 可根据实际需求调整过滤范围

第二步：执行正确的分组聚合

你原语句的问题是GROUP BY子句里错误包含了需要聚合的Hedges、AIPTotal、Value字段，导致聚合无效。另外Hedges是字符串类型，存储了NULL字符串和数字，求和前需要先做类型转换。
正确的查询语句如下：

SELECT 
    GasYearName, 
    GasYear,
    dvsAIPGroup_ID, 
    dvsAIPGroup_Name, 
    dvsInstrumentType_Descript,
    dvsRegion_ID,
    dvsRegion_Name, 
    dvsSuperRegion_Name, 
    SUM(CASE WHEN Hedges = N'NULL' THEN 0 ELSE CAST(Hedges AS INT) END) AS Hedges, 
    SUM(AIPTotal) AS AIPTotal, 
    SUM(Value) AS Value, 
    AsOfDate
FROM AIPBalance_WeeklySnapshot
WHERE AsOfDate >= '2020-06-05'
GROUP BY 
    GasYearName, 
    GasYear,
    dvsAIPGroup_ID, 
    dvsAIPGroup_Name, 
    dvsInstrumentType_Descript,
    dvsRegion_ID,
    dvsRegion_Name, 
    dvsSuperRegion_Name, 
    AsOfDate

一次性覆盖原表操作（可选，建议先备份）

如果需要直接把聚合结果替换原表数据，可以按以下步骤执行：

1. 备份原表数据：

SELECT * INTO AIPBalance_WeeklySnapshot_Bak FROM AIPBalance_WeeklySnapshot

2. 删除原表中需要处理的时间范围内的旧数据：

DELETE FROM AIPBalance_WeeklySnapshot WHERE AsOfDate >= '2020-06-05'

3. 将聚合后的结果插回原表：

INSERT INTO AIPBalance_WeeklySnapshot
SELECT 
    GasYearName, 
    GasYear,
    dvsAIPGroup_ID, 
    dvsAIPGroup_Name, 
    dvsInstrumentType_Descript,
    dvsRegion_ID,
    dvsRegion_Name, 
    dvsSuperRegion_Name, 
    CAST(SUM(CASE WHEN Hedges = N'NULL' THEN 0 ELSE CAST(Hedges AS INT) END) AS NVARCHAR(4)) AS Hedges, 
    SUM(AIPTotal) AS AIPTotal, 
    SUM(Value) AS Value, 
    AsOfDate
FROM AIPBalance_WeeklySnapshot_Bak
WHERE AsOfDate >= '2020-06-05'
GROUP BY 
    GasYearName, 
    GasYear,
    dvsAIPGroup_ID, 
    dvsAIPGroup_Name, 
    dvsInstrumentType_Descript,
    dvsRegion_ID,
    dvsRegion_Name, 
    dvsSuperRegion_Name, 
    AsOfDate

内容的提问来源于stack exchange，提问作者Brent