是的，你可以用in和subselect来做

select * 
from shoes
where shoe.id in (
    select shoe.id
    from shoes
    where production_step = 2
    and result = 1
)
and production_step = 3

这可能是一种选择；参见代码中的注解（第1-12行表示示例数据；你已经有了，别打了。您可能感兴趣的查询从第13行开始。

SQL> with shoes (shoe_nr, production_step, result) as
  2  -- sample data
  3    (select 1, 1, 1    from dual union all
  4     select 1, 2, 1    from dual union all
  5     select 1, 3, null from dual union all
  6     select 2, 1, 1    from dual union all
  7     select 2, 2, 1    from dual union all
  8     select 2, 3, null from dual union all
  9     select 3, 1, null from dual union all
 10     select 3, 2, null from dual union all
 11     select 3, 3, null from dual
 12    ),
 13  -- which shoes' production step #3 should be skipped?
 14  skip as
 15    (select shoe_nr
 16     from shoes
 17     where production_step = 2
 18       and result = 1
 19    )
 20  -- finally:
 21  select a.shoe_nr, a.production_step, a.result
 22  from shoes a
 23  where (a.shoe_nr, a.production_step) not in (select b.shoe_nr, 3
 24                                               from skip b
 25                                              )
 26  order by a.shoe_nr, a.production_step;

   SHOE_NR PRODUCTION_STEP     RESULT
---------- --------------- ----------
         1               1          1
         1               2          1
         2               1          1
         2               2          1
         3               1
         3               2
         3               3

7 rows selected.

SQL>

如果你只是想 shoe_nr 如果满足条件，可以使用聚合和 having 条款：

select shoe_nr
from mytable
group by shoe_nr
having 
    max(case when production_step = 2 then result end) = 0
    and max(case when production_step = 3 then 1 end) = 1

如果你想让整行都和这个对应 shoe_nr 在步骤3中，请改用窗口函数：

select 1
from (
    select 
        t.*, 
        max(case when production_step = 2 then result end) 
            over(partition by shoe_nr) as has_completed_step_2
    from mytable t
) t
where production_step = 3 and has_completed_step_2 = 0