您可以使用string\u agg将状态列的文本组合成一个字符串，并检查该字符串是否包含“running”或“failed”

select report_id, 
case when STRING_AGG(status, '') like '%Running%' then 'Running'
when STRING_AGG(status, '') like '%Failed%' then 'Failed'
else 'Completed' end Status,
max(date) Date
from abc
group by report_id

查看您的样本，您似乎需要每个报告的初始状态（基于日期）

select m.* 
from my_table m
inner join (
    select Report_id,  min(date) min_date
    from my_table  
    group by Report_id
) t on t.Report_id = m. and t.min_date  = m.date

你可以试着用 row_number() ```
select * from
(
select report_id,status,date, row_number() over(partition by report_id order by
case when status in ('Running','Failed') then 1 else 2 end asc,Date desc) as rn
from tablename
)A where rn=1

不确定这是否是你要找的，也不知道这是否是完全运行。

select report_id,
    case when q.failed_sum > 0 then 'Failed'
         when q.running_sum > 0 then 'Running'
        else  'Completed'
    end,
    max(date)
    from table inner join
        (
            select report_id, 
            sum(case when status = 'Failed' then 1 end) as failed_sum,
            sum(case when status = 'Running' then 1 end) as running_sum,
            sum(case when status = 'Completed' then 1 end)as completed_sum
            from table 
            group by report_id
        )q on report_id = q.report_id
    where status = (case when failed_sum > 0 then 'Failed'
        else when running_sum > 0 then 'Running'
        else then 'Completed'
    end) 
    group by report_id

你可以用 distinct on :

select distinct on (record_id) t.*
from t
order by (case when status <> 'Completed' then 1 else 2 end),
         date desc;