我需要写一个查询，以找出是否有任何订单后，断开订单的基础上，订单日期。
添加一个PARTITION BY子句，并将ORDER BY子句从ASC更改为DESC，以便最新的行始终排名第一，然后您可以检查排名第一的行的order_id是否为Disconnect。如果你需要检查至少有一行的order_id和Disconnect，那么你可以使用COUNT解析函数和CASE表达式：

SELECT *
FROM   (
  SELECT account_no,
         order_id,
         order_date,
         RANK() OVER (
           PARTITION BY account_no
           ORDER BY created_t DESC
         ) AS rank_no,
         COUNT(CASE order_id WHEN 'Disconnect' THEN 1 END) OVER (
           PARTITION BY account_no
         ) AS has_disconnect
  FROM   table_name
)
WHERE rnk      = 1
AND   order_id != 'Disconnect'
AND   has_disconnect > 0;

或者，在Oracle 12中，您可以使用MATCH_RECOGNIZE：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY account_no
  ORDER BY created_t DESC
  ALL ROWS PER MATCH
  PATTERN ( ^ not_disconnect {- any_row*? disconnect -} )
  DEFINE
    not_disconnect AS order_id != 'Disconnect',
    disconnect     AS order_id =  'Disconnect'
);

您也可以使用EXISTS来找出这样的条件，以获得具有相同account_no的Disconnect的类型不同的行：

select d.* from table_name d
where order_id <> 'Disconnect'
    and exists(
        select 1 from table_name d1 
            where d1.account_no = d.account_no 
            -- or <= depending on your requirements
            and d1.order_date < d.order_date
            and d1.order_id = 'Disconnect'
    )
;

如果您希望Disconnect行后面有一个not Disconnect：

select d.* from table_name d
where order_id = 'Disconnect'
    and exists(
        select 1 from table_name d1 
            where d1.account_no = d.account_no 
            -- or >= depending on your requirements
            and d1.order_date > d.order_date
            and d1.order_id <> 'Disconnect'
    )
;