如何按Desc_Cars汇总不同TabID的Dist与Days列求和结果？

可以通过条件聚合实现需求，避免冗余子查询，同时精准按Desc_Cars分组计算目标字段：

SELECT
    Desc_Cars,
    -- 计算Sum_Dist：TabID=85的Dist1总和 + TabID=86的Dist2总和
    SUM(CASE WHEN TabID = 85 THEN Dist1 ELSE 0 END) +
    SUM(CASE WHEN TabID = 86 THEN Dist2 ELSE 0 END) AS Sum_Dist,
    -- 计算Sum_Days：TabID=85的Days1+Days2总和 + TabID=86的Days3总和
    SUM(CASE WHEN TabID = 85 THEN Days1 + Days2 ELSE 0 END) +
    SUM(CASE WHEN TabID = 86 THEN Days3 ELSE 0 END) AS Sum_Days
FROM Details
WHERE DocID = 16
GROUP BY Desc_Cars;

逻辑说明 #

• 用CASE语句筛选对应TabID的目标字段，非目标场景补0避免影响求和结果
• 通过SUM()聚合函数，按Desc_Cars分组统计符合条件的字段总和
• 相比原查询的子查询写法，条件聚合只需扫描一次表，性能更优，也更易维护

如果需要保留TabID=85/86对应的其他列值，可以结合CTE先完成聚合，再关联原表获取额外字段：

WITH AggregatedData AS (
    SELECT
        Desc_Cars,
        SUM(CASE WHEN TabID = 85 THEN Dist1 ELSE 0 END) +
        SUM(CASE WHEN TabID = 86 THEN Dist2 ELSE 0 END) AS Sum_Dist,
        SUM(CASE WHEN TabID = 85 THEN Days1 + Days2 ELSE 0 END) +
        SUM(CASE WHEN TabID = 86 THEN Days3 ELSE 0 END) AS Sum_Days
    FROM Details
    WHERE DocID = 16
    GROUP BY Desc_Cars
)
SELECT
    d.ID,
    d.Desc_Cars,
    d.DocID,
    d.TabID,
    d.Dist1, d.Dist2, d.Days1, d.Days2, d.Days3,
    ad.Sum_Dist,
    ad.Sum_Days
FROM Details d
JOIN AggregatedData ad ON d.Desc_Cars = ad.Desc_Cars
WHERE d.DocID = 16;

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