SQL中基于其他计算列生成新列的语法错误修正求助

原SQL代码存在三个语法/逻辑问题：

• 计算Activity_Valid和Column C的两个表达式之间缺少逗号，导致语法报错
• 列名Column C包含空格，需要用反引号（MySQL）或双引号（如PostgreSQL、SQL Server）包裹，否则数据库无法识别
• 无法在同一SELECT子句中直接引用刚定义的别名Activity_Valid来计算后续字段，因为SQL会先执行所有SELECT表达式，再为结果列分配别名

以下是几种可行的修正写法：

方法一：使用子查询先计算Activity_Valid #

WITH table_1 AS (
    SELECT Order_Number, Event_Name
    FROM Activity
),
Table_2 AS (
    SELECT Order_Number, Order_Status
    FROM `Order` -- Order是SQL关键字，建议用反引号包裹避免冲突
)
SELECT 
    sub.Order_Number,
    sub.Activity_Valid,
    IF(sub.Activity_Valid = 'Y', 'Z', 'Z1') AS `Column C`
FROM (
    SELECT 
        A.Order_Number,
        IF(A.Event_Name = 'x', 'Y', 'Y1') AS Activity_Valid
    FROM Table_1 A
    LEFT JOIN Table_2 B ON A.Order_Number = B.Order_Number
) AS sub

方法二：重复Activity_Valid的计算逻辑 #

如果不想用子查询，可以直接重复Activity_Valid的表达式来计算Column C：

WITH table_1 AS (
    SELECT Order_Number, Event_Name
    FROM Activity
),
Table_2 AS (
    SELECT Order_Number, Order_Status
    FROM `Order`
)
SELECT 
    A.Order_Number, 
    IF(A.Event_Name = 'x', 'Y', 'Y1') AS Activity_Valid,
    IF(IF(A.Event_Name = 'x', 'Y', 'Y1') = 'Y', 'Z', 'Z1') AS `Column C`
FROM Table_1 A 
LEFT JOIN Table_2 B ON A.Order_Number = B.Order_Number

方法三：在CTE中提前计算Activity_Valid #

WITH table_1 AS (
    SELECT Order_Number, Event_Name
    FROM Activity
),
Table_2 AS (
    SELECT Order_Number, Order_Status
    FROM `Order`
),
Activity_Calc AS (
    SELECT 
        A.Order_Number,
        IF(A.Event_Name = 'x', 'Y', 'Y1') AS Activity_Valid,
        B.Order_Status
    FROM Table_1 A
    LEFT JOIN Table_2 B ON A.Order_Number = B.Order_Number
)
SELECT 
    Order_Number,
    Activity_Valid,
    IF(Activity_Valid = 'Y', 'Z', 'Z1') AS `Column C`
FROM Activity_Calc

注：代码中的x、Y、Y1、Z、Z1如果是字符串常量，需要用单引号包裹；不同数据库的IF函数语法可能略有差异（如PostgreSQL用CASE WHEN），可根据实际使用的数据库调整。

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