如何在SQL查询中计算OPS列并汇总所选字段总计？

针对你的需求，以下是修改后的SQL语句，同时实现OPS列添加和总计值统计：

WITH PlayerBattingStats AS (
    SELECT 
        nameFirst + ' ' + nameLast AS NAME,
        G,
        AB,
        R,
        H,
        S,
        B2 AS '2B',
        B3 AS '3B',
        HR,
        RBI,
        (S + B2*2 + B3*3 + HR*4) AS TB,
        BB,
        SO,
        SB,
        CASE WHEN AB = 0 THEN 0 ELSE H*1.0/AB END AS AVG,
        CASE WHEN (AB + BB + HBP + SF) = 0 THEN 0 ELSE (H + BB + HBP)*1.0/(AB + BB + HBP + SF) END AS OBS,
        CASE WHEN AB = 0 THEN 0 ELSE (S + B2*2 + B3*3 + HR*4)*1.0/AB END AS SLG
    FROM vwPlayersBatting
    WHERE teamID = 'hou' AND yearID = '2005'
)
SELECT 
    COALESCE(NAME, '总计') AS NAME,
    SUM(G) AS G,
    SUM(AB) AS AB,
    SUM(R) AS R,
    SUM(H) AS H,
    SUM(S) AS S,
    SUM([2B]) AS '2B',
    SUM([3B]) AS '3B',
    SUM(HR) AS HR,
    SUM(RBI) AS RBI,
    SUM(TB) AS TB,
    SUM(BB) AS BB,
    SUM(SO) AS SO,
    SUM(SB) AS SB,
    -- 总计行AVG用全队总安打/总打数计算
    CASE 
        WHEN GROUPING(NAME) = 1 THEN CASE WHEN SUM(AB) = 0 THEN 0 ELSE SUM(H)*1.0/SUM(AB) END
        ELSE AVG(AVG) 
    END AS AVG,
    -- 总计行OBS用全队总上垒数/总打席数计算
    CASE 
        WHEN GROUPING(NAME) = 1 THEN CASE WHEN SUM(AB + BB + HBP + SF) = 0 THEN 0 ELSE SUM(H + BB + HBP)*1.0/SUM(AB + BB + HBP + SF) END
        ELSE AVG(OBS) 
    END AS OBS,
    -- 总计行SLG用全队总垒打/总打数计算
    CASE 
        WHEN GROUPING(NAME) = 1 THEN CASE WHEN SUM(AB) = 0 THEN 0 ELSE SUM(TB)*1.0/SUM(AB) END
        ELSE AVG(SLG) 
    END AS SLG,
    -- OPS列及总计行计算
    CASE 
        WHEN GROUPING(NAME) = 1 THEN CASE WHEN SUM(AB + BB + HBP + SF) = 0 OR SUM(AB) = 0 THEN 0 ELSE (SUM(H + BB + HBP)*1.0/SUM(AB + BB + HBP + SF)) + (SUM(TB)*1.0/SUM(AB)) END
        ELSE AVG(OBS) + AVG(SLG) 
    END AS OPS
FROM PlayerBattingStats
GROUP BY ROLLUP(NAME)
ORDER BY 
    CASE WHEN GROUPING(NAME) = 1 THEN 1 ELSE 0 END, -- 让总计行显示在最后
    AB DESC;

关键实现说明 #

• 添加OPS列：直接通过OBS + SLG计算得到，用CTE封装基础统计字段，避免重复编写计算逻辑，提升代码可读性。
• 获取总计值：
  • 用GROUP BY ROLLUP(NAME)自动生成总计行，GROUPING(NAME)返回1时表示当前行是总计行。
  • 总计行的AVG、OBS、SLG、OPS需要用全队累计数据重新计算，而非简单取平均值，保证统计逻辑准确。
  • 通过COALESCE(NAME, '总计')将总计行的名称替换为“总计”，方便识别。

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