查询AV先于VL的连续行时遇ORA-00928错误求助

一、ORA-00928错误原因 #

Oracle的WITH子句（CTE）仅负责定义临时数据集，必须在CTE定义后跟随一个SELECT语句来查询这些临时表。你的代码只定义了ls_ital和t1两个CTE，但没有后续的主查询逻辑，因此触发该语法错误。

二、语法修正 #

先补上主查询模块，修正后的基础可执行代码如下：

with ls_ital as
    (select ls_key, ls_letter, ls_status, ls_insertdate, 
            row_number() over (partition by ls_letter order by ls_key) as id
     from letter_status_aital),
  t1 as  
    (select ls_key, ls_letter, ls_status, ls_insertdate, id,
            row_number() over(partition by ls_letter order by id) as rn,
            id - row_number() over(partition by ls_letter order by id) as diff
     from ls_ital
     where ls_status in ('AV','VL'))
-- 主查询：从CTE t1中检索数据
select ls_key, ls_letter, ls_status, ls_insertdate
from t1;

三、查询思路调整 #

你的原思路未按ls_letter分区，无法针对同一letter内的状态序列做分析，需调整窗口函数的分区逻辑：

• 在ls_ital的row_number()中添加partition by ls_letter，确保每个letter内部按ls_key生成连续ID；
• 在t1的两个窗口函数中同样添加partition by ls_letter，保证分组计算的是同一letter内的序列。

四、实现需求的最终SQL #

要筛选出同一letter下**'AV'在前、'VL'在后的连续行**，可以通过lag()和lead()函数获取相邻行状态来实现，完整SQL如下：

with ls_ital as
    (select ls_key, ls_letter, ls_status, ls_insertdate,
            row_number() over (partition by ls_letter order by ls_key) as id
     from letter_status_aital
     where ls_status in ('AV','VL')),
  adjacent_status as
    (select ls_key, ls_letter, ls_status, ls_insertdate, id,
            lag(ls_status) over (partition by ls_letter order by id) as prev_status,
            lead(ls_status) over (partition by ls_letter order by id) as next_status
     from ls_ital)
select ls_key, ls_letter, ls_status, ls_insertdate
from adjacent_status
-- 筛选AV后紧跟VL的行对：要么是AV且下一行是VL，要么是VL且上一行是AV
where (ls_status = 'AV' and next_status = 'VL')
   or (ls_status = 'VL' and prev_status = 'AV');

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