SQLite报错no such column: X 如何让别名X可被CASE语句提前调用

你遇到的是SQL逻辑执行顺序的典型问题：同层级SELECT子句中，别名无法被同层其他表达式调用，可通过以下几种方案解决： #

方案1：重复X的计算逻辑（最简易，无需嵌套） #

直接把X的计算表达式写在CASE语句中即可，同时修正原有代码的两处语法错误：

1. ROUND函数括号匹配错误，原写法ROUND((a-b)),2)多了右括号
2. WHERE条件中字符串常量推荐使用单引号，避免和标识符引用规则冲突
3. 补充修正GROUP BY的规范问题（多数数据库要求SELECT中未聚合的列都要加入GROUP BY，你可根据自身数据库规则调整）

SELECT 
    e, a, b,
    ROUND((a-b),2) AS [X],
    CASE WHEN ROUND((a-b),2) BETWEEN 5 AND 10 THEN 'Good Choice'
         ELSE 'bad choice' END AS 'choice'
FROM table_1
LEFT JOIN table_2 ON table_1.column_1 = table_2.column_2
WHERE BBB = 'VVV' OR BBB = 'CCC'
GROUP BY column_1, e, a, b; -- 补充未聚合列适配严格SQL模式

方案2：子查询嵌套（逻辑清晰，无需重复写计算逻辑） #

把X的计算放到内层子查询中，外层查询即可直接调用X的别名：

SELECT 
    e, a, b, X,
    CASE WHEN X BETWEEN 5 AND 10 THEN 'Good Choice'
         ELSE 'bad choice' END AS 'choice'
FROM (
    SELECT 
        e, a, b, ROUND((a-b),2) AS [X], column_1, BBB
    FROM table_1
    LEFT JOIN table_2 ON table_1.column_1 = table_2.column_2
) AS t
WHERE BBB = 'VVV' OR BBB = 'CCC'
GROUP BY column_1, e, a, b, X;

方案3：CTE（公共表表达式，适合复杂查询场景） #

和子查询逻辑类似，结构更易读，MySQL 8.0+、PostgreSQL、SQL Server等主流数据库均支持：

WITH calc_x AS (
    SELECT 
        e, a, b, ROUND((a-b),2) AS [X], column_1, BBB
    FROM table_1
    LEFT JOIN table_2 ON table_1.column_1 = table_2.column_2
)
SELECT 
    e, a, b, X,
    CASE WHEN X BETWEEN 5 AND 10 THEN 'Good Choice'
         ELSE 'bad choice' END AS 'choice'
FROM calc_x
WHERE BBB = 'VVV' OR BBB = 'CCC'
GROUP BY column_1, e, a, b, X;

方案4：LATERAL JOIN（适合多重复用计算字段场景） #

如果你的数据库支持LATERAL JOIN（PostgreSQL、MySQL 8.0.14+、SQL Server），可以用该方式提前计算X，后续所有逻辑都可以直接复用别名：

SELECT 
    e, a, b, x.X,
    CASE WHEN x.X BETWEEN 5 AND 10 THEN 'Good Choice'
         ELSE 'bad choice' END AS 'choice'
FROM table_1
LEFT JOIN table_2 ON table_1.column_1 = table_2.column_2
LEFT JOIN LATERAL (SELECT ROUND((a-b),2) AS X) AS x ON TRUE
WHERE BBB = 'VVV' OR BBB = 'CCC'
GROUP BY column_1, e, a, b, x.X;

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