SQL递归CTE中WHERE子句引用别名seqnum1报‘无效列名’错误的原因咨询

为什么WHERE seqnum1 = 1会报错？ #

这是SQL语句的执行顺序导致的核心问题！SQL引擎处理查询子句的优先级是固定的：

1. FROM/JOIN（先确定数据源）
2. WHERE（过滤行）
3. GROUP BY/HAVING（分组及过滤分组）
4. SELECT（生成列、定义别名）
5. ORDER BY（排序）

你在递归CTE的SELECT里定义了seqnum1这个别名，但WHERE子句是在SELECT之前执行的——这时候seqnum1还没被创建出来，SQL引擎根本识别不了这个列名，自然会抛出Invalid column name 'seqnum1'的错误。

而换成seqnum时不报错，是因为锚点CTE（递归的起始部分）已经定义了seqnum列，SQL引擎能找到这个列，但此时过滤的是锚点里固定为0的seqnum值，完全不符合你要筛选递归部分row_number=1的需求，所以结果不符合预期。

正确写法：先计算row_number，再过滤 #

要实现你想要的逻辑，需要把row_number的计算嵌套到子查询里，先生成seqnum1，再在外层WHERE中过滤。修改后的完整SQL如下：

declare @department_code varchar(8), @basedate varchar(8); 
set @department_code = 'A'; 
set @basedate = '20200501'; 
Create table #DEPARTMENT_MT(DEPARTMENT_CODE varchar(8), REVISION_DATE varchar(8), PARENT_DEPARTMENT_CODE varchar(8), DEL_FLG bit); 
insert into #DEPARTMENT_MT values('A', '20200101', 'X', 0); 
insert into #DEPARTMENT_MT values('A', '20220101', '', 0); 
insert into #DEPARTMENT_MT values('B', '20200101', 'A', 0); 
insert into #DEPARTMENT_MT values('B', '20220101', '', 0); 
insert into #DEPARTMENT_MT values('C', '20200101', 'B', 0); 
insert into #DEPARTMENT_MT values('C', '20220101', 'A', 0); 
insert into #DEPARTMENT_MT values('D', '20200101', 'C', 0); 
insert into #DEPARTMENT_MT values('D', '20220101', 'F', 0); 
insert into #DEPARTMENT_MT values('E', '20200101', 'D', 0); 
insert into #DEPARTMENT_MT values('F', '20200101', 'E', 0); 
insert into #DEPARTMENT_MT values('G', '20200101', 'F', 0); 
insert into #DEPARTMENT_MT values('H', '20200101', 'G', 0); 

;with cte as ( 
 -- 锚点部分：获取起始部门的最新历史记录
 select *, cast(0 as bigint) as seqnum 
 from #DEPARTMENT_MT 
 where DEPARTMENT_CODE=@department_code 
   and REVISION_DATE = (select max(REVISION_DATE) from #DEPARTMENT_MT where REVISION_DATE < @basedate and DEPARTMENT_CODE=@department_code) 
 union all 
 -- 递归部分：先通过子查询计算row_number，再筛选符合条件的行
 select sub.*, sub.seqnum1 as seqnum
 from (
     select t.*, 
            row_number() over (partition by t.DEPARTMENT_CODE order by t.REVISION_DATE desc) as seqnum1 
     from #DEPARTMENT_MT t 
     inner join cte on cte.DEPARTMENT_CODE = t.PARENT_DEPARTMENT_CODE 
                   AND t.REVISION_DATE <= cte.REVISION_DATE
 ) sub
 where sub.seqnum1 = 1
) 
select * from cte;

小优化说明 #

原代码中row_number()的分区条件是partition by t.DEPARTMENT_CODE, t.REVISION_DATE，其实只需要按DEPARTMENT_CODE分区就足够了——同一个部门同一日期的记录，按日期倒序排序后row_number必然是1，多加上日期分区属于冗余操作，所以我做了简化调整。

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