如何修正WITH子句中SELECT t1.*时的SQL语法错误？

这个错误的根源有两处，咱们一步步拆解修正：

1. CTE内部的语法结构错误 #

你在test这个公共表表达式（CTE）里，内层子查询t1结束后直接写了select t1.* where row_rank =1，这不符合SQL语法规范——要引用子查询t1，必须通过FROM关键字，而且过滤条件WHERE要放在FROM t1之后。

原错误部分的修正：

with test as (
   -- 外层select需要从t1子查询中获取数据，再添加过滤条件
   select * 
   from (
      select id, name, department, org, time, reporttime, scheduled_time, address
            ,row_number() over (partition by id order by time desc, reporttime desc, scheduled_time desc) AS row_rank 
      from table1
   ) t1
   where row_rank = 1  -- 过滤出每个id对应的最新记录
)

如果你用的是支持QUALIFY关键字的数据库（比如Snowflake、BigQuery、Databricks SQL等），还可以简化写法，省去一层子查询：

with test as (
   select id, name, department, org, time, reporttime, scheduled_time, address
         ,row_number() over (partition by id order by time desc, reporttime desc, scheduled_time desc) AS row_rank 
   from table1
   qualify row_rank = 1  -- 直接在窗口函数后完成过滤
)

2. 主查询中的别名引用错误 #

你的主查询里，from test没有给这个CTE设置别名t1，但后面却用了t1.*和t1.id，这会导致数据库找不到t1这个对象。需要给test起别名t1，或者直接用test作为别名。

修正后的主查询部分：

select t1.*, t2.id, t2.new_org, t2.date
from test as t1  -- 给CTE设置别名t1
left outer join table2 as t2
     on t1.id = t2.id
where t2.new_org != "A" 

完整修正后的代码 #

把两部分合起来，最终的正确代码如下（标准SQL写法）：

with test as (
   select * 
   from (
      select id, name, department, org, time, reporttime, scheduled_time, address
            ,row_number() over (partition by id order by time desc, reporttime desc, scheduled_time desc) AS row_rank 
      from table1
   ) t1
   where row_rank = 1
)

select t1.*, t2.id, t2.new_org, t2.date
from test as t1
left outer join table2 as t2
     on t1.id = t2.id
where t2.new_org != "A" 

这样修改后，语法错误就会消失，同时逻辑也能正确实现：先筛选出每个id对应的最新记录，再和table2做左连接并过滤掉new_org为"A"的记录。

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