SQL如何基于前两行数据为各列计算对应的百分比值

实现代码（SQL Server 语法） #

WITH UnpivotedData AS (
    -- 逆透视月份列为行
    SELECT 
        Status,
        MonthName,
        Value
    FROM CallStatus
    UNPIVOT (
        Value FOR MonthName IN (APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC,JAN,FEB,MAR)
    ) AS UnpivResult
),
MonthPercent AS (
    -- 按月份计算完成占比
    SELECT
        MonthName,
        ROUND(
            SUM(CASE WHEN Status = 'Complete' THEN Value ELSE 0 END) * 100.0 / SUM(Value),
            2 -- 保留2位小数，可按需调整
        ) AS PercentValue
    FROM UnpivotedData
    GROUP BY MonthName
),
MergeData AS (
    -- 合并原有状态数据与百分比行数据
    SELECT Status, MonthName, CAST(Value AS FLOAT) AS Value FROM UnpivotedData
    UNION ALL
    SELECT '完成占比(%)' AS Status, MonthName, PercentValue AS Value FROM MonthPercent
)
-- 透视回原有列结构
SELECT 
    Status,
    APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC,JAN,FEB,MAR
FROM MergeData
PIVOT (
    SUM(Value)
    FOR MonthName IN (APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC,JAN,FEB,MAR)
) AS PivResult
-- 控制行顺序：Ongoing → Complete → 占比
ORDER BY 
    CASE Status 
        WHEN 'Ongoing' THEN 1
        WHEN 'Complete' THEN 2
        ELSE 3
    END

逻辑说明 #

• 原有思路没有问题，仅缺失合并百分比行的步骤，上述代码沿用逆透视+透视的核心逻辑，补充了占比计算和数据合并环节
• 若使用非SQL Server数据库，只需将UNPIVOT/PIVOT语法替换为对应数据库的等效写法即可，整体计算逻辑通用
• 如果不需要保留小数，删掉ROUND函数即可，也可以调整第二个参数修改小数保留位数

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