sql - 查询根据特定条件过滤记录

Question

There is a table a_status_check with the following data:

要求是:如果状态LC和BE都存在，那么只考虑LC。否则，请考虑 BE。忽略该 ID 的其他代码。

所以，结果应该是这样的:

我尝试了 DECODE 和 CASE 函数，但没有成功。有人可以帮忙吗。

Answer 1

使用分析函数:

select distinct
  id,
  first_value (status) over (partition by id order by status desc) status,
  first_value (amt   ) over (partition by id order by status desc) amt
from
  tq84_a_status_check
where
  status in ('LC', 'BE')
order by
  id;

测试数据:

create table tq84_a_status_check (
  id number,
  status varchar2(10),
  amt number
);

select distinct
  id,
  first_value (status) over (partition by id order by status desc) status,
  first_value (amt   ) over (partition by id order by status desc) amt
from
  tq84_a_status_check
where
  status in ('LC', 'BE')
order by
  id;