如何基于出现次数对SQL多列数值求和并计算总工时

核心计算逻辑 #

根据你的规则，单个类别的总工时计算公式为：
HrsFirstOccur + (Occurrences - 1) * HrsAddlOccur

• 首次出现取HrsFirstOccur
• 剩余Occurrences - 1次每次取HrsAddlOccur

1. 查询每个类别的明细及对应总工时 #

修改原查询，直接添加类别总工时计算列：

SELECT 
    c.Category,
    c.HrsFirstOccur,
    c.HrsAddlOccur,
    COUNT(*) AS Occurrences,
    -- 计算当前类别的总工时
    c.HrsFirstOccur + (COUNT(*) - 1) * c.HrsAddlOccur AS CategoryTotalHrs
FROM dbo.Categories sc
INNER JOIN dbo.Categories c 
    ON sc.CategoryID = c.CategoryID
INNER JOIN dbo.OrderHistory oh 
    ON sc.GONumber = oh.OrderNumber
    AND sc.Item = oh.ItemNumber
WHERE sc.BusinessGroupID = 1
    AND oh.OrderNumber = 500
    AND oh.ItemNumber = '100'
GROUP BY c.Category, c.HrsFirstOccur, c.HrsAddlOccur

执行后会得到每个类别的总工时：

2. 直接计算所有类别的总工时总计 #

如果只需要最终的总计值，可以用子查询嵌套求和：

SELECT SUM(CategoryTotalHrs) AS TotalHrs
FROM (
    SELECT 
        c.HrsFirstOccur + (COUNT(*) - 1) * c.HrsAddlOccur AS CategoryTotalHrs
    FROM dbo.Categories sc
    INNER JOIN dbo.Categories c 
        ON sc.CategoryID = c.CategoryID
    INNER JOIN dbo.OrderHistory oh 
        ON sc.GONumber = oh.OrderNumber
        AND sc.Item = oh.ItemNumber
    WHERE sc.BusinessGroupID = 1
        AND oh.OrderNumber = 500
        AND oh.ItemNumber = '100'
    GROUP BY c.Category, c.HrsFirstOccur, c.HrsAddlOccur
) AS CategoryTotals

执行后会直接返回52.5，和你期望的结果一致。

3. 同时显示类别明细与总计行 #

如果需要在同一张结果表里既看每个类别的数据，又看总计，可以用ROLLUP分组：

SELECT 
    CASE WHEN GROUPING(c.Category) = 1 THEN '总计' ELSE c.Category END AS Category,
    CASE WHEN GROUPING(c.Category) = 1 THEN NULL ELSE c.HrsFirstOccur END AS HrsFirstOccur,
    CASE WHEN GROUPING(c.Category) = 1 THEN NULL ELSE c.HrsAddlOccur END AS HrsAddlOccur,
    COUNT(*) AS Occurrences,
    SUM(c.HrsFirstOccur + (COUNT(*) - 1) * c.HrsAddlOccur) AS TotalHrs
FROM dbo.Categories sc
INNER JOIN dbo.Categories c 
    ON sc.CategoryID = c.CategoryID
INNER JOIN dbo.OrderHistory oh 
    ON sc.GONumber = oh.OrderNumber
    AND sc.Item = oh.ItemNumber
WHERE sc.BusinessGroupID = 1
    AND oh.OrderNumber = 500
    AND oh.ItemNumber = '100'
GROUP BY ROLLUP(c.Category, c.HrsFirstOccur, c.HrsAddlOccur)

结果会多出一行总计，清晰展示整体总工时。

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