如何从Oracle SQL订单序列列中返回最小序号行

你的报错是因为两个细节问题：

1. Oracle不允许给子查询别名加AS，直接写别名即可
2. 窗口函数里的sft是笔误，对应表的别名是wft

修正后的ROW_NUMBER()方案 #

这是分组取首行的常用方法，修正后代码如下：

select * from (
    select som.bi_wo_workord
    , som.BI_SO_NBR
    , wft.bi_wrkflw_task_seq_nbr
    , wf.bi_dist_ofc_cd
    , wft.bi_work_event_cd
    , sod.bi_map_loc_nbr
    , row_number() over (partition by som.bi_so_nbr order by wft.bi_wrkflw_task_seq_nbr) as row_num 
    from bi_so_master som
    join bi_so_det sod on sod.bi_so_nbr = som.bi_so_nbr    
    join bi_wrkflw_tasks wft on wft.bi_wrkflw_key = sod.bi_wrkflw_key
    join bi_wrkflw wf on wf.bi_wrkflw_key = sod.bi_wrkflw_key
    where som.bi_open_dt > '01-JAN-24'
    and wft.bi_work_event_cd in ('QUEUE','START')
) grouped_task_seq
where row_num = 1;

另一种：聚合函数关联方案 #

如果觉得窗口函数不好理解，也可以先找出每个服务订单的最小序号，再关联原表获取完整数据：

select som.bi_wo_workord
, som.BI_SO_NBR
, wft.bi_wrkflw_task_seq_nbr
, wf.bi_dist_ofc_cd
, wft.bi_work_event_cd
from bi_so_master som
join bi_so_det sod on sod.bi_so_nbr = som.bi_so_nbr    
join bi_wrkflw_tasks wft on wft.bi_wrkflw_key = sod.bi_wrkflw_key
join bi_wrkflw wf on wf.bi_wrkflw_key = sod.bi_wrkflw_key
-- 关联子查询获取每个订单的最小序号
join (
    select sod2.bi_so_nbr, min(wft2.bi_wrkflw_task_seq_nbr) as min_seq
    from bi_so_det sod2
    join bi_wrkflw_tasks wft2 on wft2.bi_wrkflw_key = sod2.bi_wrkflw_key
    where wft2.bi_work_event_cd in ('QUEUE','START')
    group by sod2.bi_so_nbr
) min_seq_sub on som.bi_so_nbr = min_seq_sub.bi_so_nbr 
              and wft.bi_wrkflw_task_seq_nbr = min_seq_sub.min_seq
where som.bi_open_dt > '01-JAN-24'
and wft.bi_work_event_cd in ('QUEUE','START')
order by som.bi_so_nbr;

两种方法都能得到你期望的结果：每个服务订单只返回序号最小的那一行。

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