您可以使用条件聚合和CASE表达式：

SELECT   order_number,
         CASE
         WHEN COUNT(CASE WHEN order_status = 'Approved - Unit Level' THEN 1 END) = 0
         THEN 'N' ELSE 'Y' END AS is_approved_unit,
         CASE
         WHEN COUNT(CASE WHEN order_status = 'Approved - Project Level' THEN 1 END) = 0
         THEN 'N' ELSE 'Y' END AS is_approved_project
FROM     orders
GROUP BY order_number
HAVING   COUNT(CASE WHEN rank = 1 AND order_status = 'Fulfilled' THEN 1 END) > 0

字符串
其中，对于样本数据：

CREATE TABLE orders ( order_region, order_number, order_status, rank ) AS
SELECT 'New York',  1234,   'Fulfilled',    1 FROM DUAL UNION ALL
SELECT 'New York',  1234,   'shippped', 2 FROM DUAL UNION ALL
SELECT 'New York',  1234,   'Approved - Unit Level',    3 FROM DUAL UNION ALL
SELECT 'New York',  1234,   'Queued - Unit Level',  4 FROM DUAL UNION ALL
SELECT 'New York',  1234,   'Approved - Project Level', 5 FROM DUAL UNION ALL
SELECT 'New York',  1234,   'Queued - Project Level',   6 FROM DUAL UNION ALL
SELECT 'New York',  1234,   'Submitted',    7 FROM DUAL;

型
输出：
| IS_APPROVED_UNIT|已批准项目| IS_APPROVED_PROJECT |
| --|--| ------------ |
| Y轴|Y轴| Y |
fiddle

听起来使用exists可以帮助解决这个问题：

select order_region
from orders as ordersFulfilled
where ordersFulfilled.rank = 1
and ordersFulfilled.order_status = 'Fulfilled'
and exists (
    select 1
    from orders as ordersQueued
    where ordersFulfilled.order_number = ordersQueued.order_number
    and ordersFulfilled.rank > 1
    and ordersFulfilled.order_status not like 'Queued - Project level%'
);

字符串
请注意，此查询将只返回满足条件的行。如果您需要的订单不满足条件，您可以使用not exists而不是exists。
你也应该意识到在查询中比较字符串是相当“昂贵”的，尤其是在使用通配符的时候。这就是为什么我删除了数组开头的通配符，因为这更像是“starts with”而不是“contains”。但是，这仅在索引列order_status时有效。更好的方法是使用数字而不是字符串作为状态列（当然还要在此列上添加索引）。