SELECT查询时替换列值后与另一表列值对比是否可行？

报错原因 #

SQL的执行顺序为 FROM/JOIN → WHERE → SELECT，你在SELECT阶段定义的字段别名ROLL_NUM_TO_BE_COMPARED在WHERE执行阶段还未生成，因此无法直接引用。另外你的原代码存在两个逻辑错误：

• CASE判断里误写了字段t1.PARTNER_ID，需求里判断的应该是t1.Roll_Number = '00000'
• WHERE条件的判断逻辑不符合需求，你需要筛选的是转换后的源表Roll_Number和目标表Roll_Number不相等的记录，不是原Roll或者转换后Roll和目标不等

解决方案 #

方案1：直接在WHERE中复用转换逻辑（适合短逻辑场景） #

用TRIM()函数可以同时覆盖空字符串、全空格的判断，简化代码：

SELECT 
    t1.First_Name, 
    t1.Roll_Number AS source_original_roll, 
    t2.Name, 
    t2.Roll_Number AS dest_roll,
    CASE 
        WHEN t1.Roll_Number IS NULL 
            OR TRIM(t1.Roll_Number) = '' 
            OR t1.Roll_Number = '00000'
        THEN '11223' 
        ELSE t1.Roll_Number 
    END AS source_converted_roll
FROM SOURCE_STUDENT t1
INNER JOIN DESTINATION_STUDENT t2 
    ON t1.First_Name = t2.Name
WHERE 
    CASE 
        WHEN t1.Roll_Number IS NULL 
            OR TRIM(t1.Roll_Number) = '' 
            OR t1.Roll_Number = '00000'
        THEN '11223' 
        ELSE t1.Roll_Number 
    END != t2.Roll_Number;

方案2：用CTE预先处理转换逻辑（可读性更高，适合复杂逻辑场景） #

先通过CTE完成源表Roll_Number的转换，后续直接使用别名即可：

WITH converted_source AS (
    SELECT 
        First_Name,
        Roll_Number AS original_roll,
        CASE 
            WHEN Roll_Number IS NULL 
                OR TRIM(Roll_Number) = '' 
                OR Roll_Number = '00000'
            THEN '11223' 
            ELSE Roll_Number 
        END AS converted_roll
    FROM SOURCE_STUDENT
)
SELECT 
    cs.First_Name,
    cs.original_roll AS source_original_roll,
    t2.Name,
    t2.Roll_Number AS dest_roll,
    cs.converted_roll AS source_converted_roll
FROM converted_source cs
INNER JOIN DESTINATION_STUDENT t2 
    ON cs.First_Name = t2.Name
WHERE cs.converted_roll != t2.Roll_Number;

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