你可以使用group by和having子句来实现，因为返回哪一行并不重要，你可以使用min()或max()：

select ID, MAX(ANUMBER) as ANUMBER, MAX(NAME) as NAME, 'true' AS OPTION
from mytable
where OPTION = 'true'
group by ID
having sum(case when OPTION = 'true' then 1 else 0 end) > 1

如果 OPTION 是boolean，则尝试以下操作：

select ID, MAX(ANUMBER) as ANUMBER, MAX(NAME) as NAME, 'true' AS OPTION
from mytable
where OPTION
group by ID
having sum(case when OPTION then 1 else 0 end) > 1

如果你不想列出所有的列，你可以使用窗口函数row number()和累积sum()：

with cte as (
  select *, row_number() over(partition by id order by ANUMBER desc) rn, 
            sum(case when OPTION then 1 else 0 end) over(partition by id order by ANUMBER ROWS UNBOUNDED PRECEDING) as count_
  from mytable
  where OPTION
)
select *
from cte c
where rn = 1 and COUNT_ > 1

Demo here