如何用SQL同时展示Cost of Delay与WSJF计算结果两行数据

先修正原SQL中的笔误 #

你提供的WSJF计算语句里存在笔误：FROM T*应该改为FROM T1，否则会触发语法报错。

方法1：使用UNION ALL直接合并两个查询 #

这种方式直接将两个独立查询的结果合并为一个结果集，每行对应一个指标，实现起来简单直接：

SELECT 'Cost of Delay' AS VALUE, 
       SUM(`Score Value`) AS SCORE
FROM T1
WHERE Variable IN ('Revenue', 'Cost Savings', 'Strategic Value', 'Time Criticality')
UNION ALL
SELECT 'WSJF' AS VALUE,
       100 * (
           (SELECT SUM(`Score Value`) FROM T1 WHERE Variable IN ('Revenue', 'Cost Savings', 'Strategic Value', 'Time Criticality')) /
           (SELECT SUM(`Score Value`) FROM T1 WHERE Variable IN ('Legal Risk', 'Development Effort'))
       ) AS SCORE;

方法2：用CTE预计算求和值（更高效） #

通过公共表表达式（CTE）一次性计算出Cost of Delay和分母的求和结果，再基于这两个值生成两行数据，避免重复扫描表，适合数据量较大的场景：

WITH Calculations AS (
    SELECT 
        SUM(CASE WHEN Variable IN ('Revenue', 'Cost Savings', 'Strategic Value', 'Time Criticality') THEN `Score Value` ELSE 0 END) AS CoD,
        SUM(CASE WHEN Variable IN ('Legal Risk', 'Development Effort') THEN `Score Value` ELSE 0 END) AS Other
    FROM T1
)
SELECT 'Cost of Delay' AS VALUE, CoD AS SCORE FROM Calculations
UNION ALL
SELECT 'WSJF' AS VALUE, 100 * (CoD / Other) AS SCORE FROM Calculations;

兼容性替代方案（针对不支持CTE的SQL方言） #

如果你的数据库不支持CTE（比如旧版MySQL），可以将CTE替换为子查询：

SELECT 'Cost of Delay' AS VALUE, CoD AS SCORE
FROM (
    SELECT 
        SUM(CASE WHEN Variable IN ('Revenue', 'Cost Savings', 'Strategic Value', 'Time Criticality') THEN `Score Value` ELSE 0 END) AS CoD,
        SUM(CASE WHEN Variable IN ('Legal Risk', 'Development Effort') THEN `Score Value` ELSE 0 END) AS Other
    FROM T1
) AS Calc
UNION ALL
SELECT 'WSJF' AS VALUE, 100 * (CoD / Other) AS SCORE
FROM (
    SELECT 
        SUM(CASE WHEN Variable IN ('Revenue', 'Cost Savings', 'Strategic Value', 'Time Criticality') THEN `Score Value` ELSE 0 END) AS CoD,
        SUM(CASE WHEN Variable IN ('Legal Risk', 'Development Effort') THEN `Score Value` ELSE 0 END) AS Other
    FROM T1
) AS Calc;

关键注意点 #

列名Score Value包含空格，必须用反引号（MySQL）或双引号（SQL Server、PostgreSQL等）包裹，避免被解析为字符串常量。

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