在SQL查询结果中新增单独列展示Total_Hours的平均值

要在现有查询结果的每行右侧添加所有Total_Hours的全局平均值（本例中约为25.8），以下是两种可行的解决方案：

方法1：使用窗口函数（推荐，简洁高效） #

直接在SELECT中加入窗口函数AVG(SUM(HOURS_WORKED)) OVER()，它会自动计算所有分组Total_Hours的平均值并为每行返回该值：

SELECT 
    CASE
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'A' 
            THEN '20' + RIGHT(WORKORDER_LOT_ID,2)
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'J' 
            THEN '30' + RIGHT(WORKORDER_LOT_ID,2)
        ELSE ''
    END AS Serial_Number,
    SUM(HOURS_WORKED) AS Total_Hours,
    AVG(SUM(HOURS_WORKED)) OVER() AS Avg_Total_Hours  -- 新增全局平均列
FROM
    LABOR_TICKET
WHERE  
    WORKORDER_BASE_ID LIKE '23765%'
    AND WORKORDER_LOT_ID IN ('A43', 'A44', 'A45', 'A46', 'A47', 'A48', 
                             'J43', 'J44', 'J45', 'J46', 'J47', 'J48')
GROUP BY 
    CASE
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'A' THEN '20' + RIGHT(WORKORDER_LOT_ID,2)
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'J' THEN '30' + RIGHT(WORKORDER_LOT_ID,2)
        ELSE ''
    END;

方法2：使用子查询计算全局平均 #

如果你的数据库不支持窗口函数，可以通过嵌套子查询先算出符合过滤条件的分组Total_Hours的平均值，再关联到主查询结果：

SELECT 
    t.Serial_Number,
    t.Total_Hours,
    (SELECT AVG(Total_Hours) FROM (
        SELECT SUM(HOURS_WORKED) AS Total_Hours
        FROM LABOR_TICKET
        WHERE WORKORDER_BASE_ID LIKE '23765%'
            AND WORKORDER_LOT_ID IN ('A43', 'A44', 'A45', 'A46', 'A47', 'A48', 
                                     'J43', 'J44', 'J45', 'J46', 'J47', 'J48')
        GROUP BY CASE
                    WHEN LEFT(WORKORDER_LOT_ID,1) = 'A' THEN '20' + RIGHT(WORKORDER_LOT_ID,2)
                    WHEN LEFT(WORKORDER_LOT_ID,1) = 'J' THEN '30' + RIGHT(WORKORDER_LOT_ID,2)
                    ELSE ''
                END
    ) AS sub) AS Avg_Total_Hours
FROM (
    -- 原查询作为子查询
    SELECT 
        CASE
            WHEN LEFT(WORKORDER_LOT_ID,1) = 'A' 
                THEN '20' + RIGHT(WORKORDER_LOT_ID,2)
            WHEN LEFT(WORKORDER_LOT_ID,1) = 'J' 
                THEN '30' + RIGHT(WORKORDER_LOT_ID,2)
            ELSE ''
        END AS Serial_Number,
        SUM(HOURS_WORKED) AS Total_Hours
    FROM
        LABOR_TICKET
    WHERE  
        WORKORDER_BASE_ID LIKE '23765%'
        AND WORKORDER_LOT_ID IN ('A43', 'A44', 'A45', 'A46', 'A47', 'A48', 
                                 'J43', 'J44', 'J45', 'J46', 'J47', 'J48')
    GROUP BY 
        CASE
            WHEN LEFT(WORKORDER_LOT_ID,1) = 'A' THEN '20' + RIGHT(WORKORDER_LOT_ID,2)
            WHEN LEFT(WORKORDER_LOT_ID,1) = 'J' THEN '30' + RIGHT(WORKORDER_LOT_ID,2)
            ELSE ''
        END
) AS t;

预期结果 #

执行后每行的Avg_Total_Hours列都会显示全局平均值：

Serial_Number Total_Hours Avg_Total_Hours
-------------------------------------------
2043       43.97        25.8
2044       31.99        25.8
2045       42.88        25.8
2046       29.80        25.8
2047       10.97        25.8
2048       18.02        25.8
3043       8.13         25.8
3044       38.53        25.8
3045       25.73        25.8
3046       31.99        25.8
3047       16.79        25.8
3048       11.20        25.8

原查询及结果 #

原SQL语句：

SELECT 
    CASE
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'A' 
            THEN '20' + RIGHT(WORKORDER_LOT_ID,2)
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'J' 
            THEN '30' + RIGHT(WORKORDER_LOT_ID,2)
        ELSE ''
    END AS Serial_Number,
    SUM(HOURS_WORKED) AS Total_Hours
FROM
    LABOR_TICKET
WHERE  
    WORKORDER_BASE_ID LIKE '23765%'
    AND WORKORDER_LOT_ID IN ('A43', 'A44', 'A45', 'A46', 'A47', 'A48', 
                             'J43', 'J44', 'J45', 'J46', 'J47', 'J48')
GROUP BY 
    CASE
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'A' THEN '20' + RIGHT(WORKORDER_LOT_ID,2)
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'J' THEN '30' + RIGHT(WORKORDER_LOT_ID,2)
        ELSE ''
    END;

原查询结果：

Serial_Number Total_Hours
---------------------------
2043       43.97
2044       31.99
2045       42.88
2046       29.80
2047       10.97
2048       18.02
3043       8.13
3044       38.53
3045       25.73
3046       31.99
3047       16.79
3048       11.20

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