POSTGRES：如何仅在不存在另一个值时才选择具有特定值的行，在这种情况下选择另一个值？

Question

下面是我正在使用的表的一个示例。我想实现的是选择类型不是"NONE"的最近一行，除非"NONE"是该id唯一可用的类型。

无

id	日期	类型
123	01-01-2021
123	2021年12月31日	无
123	2021年1月1日	金属
123	2021年12月31日	金属

您可以将Row_number((函数与case语句一起使用，以确定要拾取的行。

with cte AS
(
select id,
date, 
type,
row_number() over(partition by id 
order by case when type <> 'NONE' THEN 1 ELSE 2 END, date desc
) as RN

from test
)
select *
from cte
where rn = 1

SQL Fiddle

不使用分析函数：SQL:

with cte
as
(
select id,type,flag, max(date) date from (select id , date , type, case when type='NONE' then 'flag_none' else 'flag_not_none' end as flag from test) x group by  id,type,flag)
select id,type,date from cte where flag='flag_not_none'  and  (id,date) in (select id,max(date) from cte group by id)
union
select id,type,date from cte where id not in (select id from cte where flag='flag_not_none'  and  (id,date) in (select id,max(date) from cte group by id)) and flag='flag_none';

完全执行：

CREATE TABLE test 
(
id int,
date date,
type text
);
insert into test
values
(123,  '01-01-2021', 'NONE'),
(123, '12-31-2021', 'NONE'),
(123, '01-01-2021', 'METAL'),
(123, '12-31-2021', 'PLASTIC'),
(124,  '01-01-2021', 'NONE'),
(124, '12-31-2021', 'NONE'),
(124, '01-01-2021', 'NONE'),
(124, '12-31-2021', 'NONE'),
(125, '12-25-2021', 'NONE'),
(125, '12-25-2021', 'RUBBER'),
(125, '12-31-2021', 'STEEL');
postgres=# with cte
postgres-# as
postgres-# (
postgres(#  select id,type,flag, max(date) date from (select id , date , type, case when type='NONE' then 'flag_none' else 'flag_not_none' end as flag from test) x group by  id,type,flag)
postgres-# select id,type,date from cte where flag='flag_not_none'  and  (id,date) in (select id,max(date) from cte group by id)
postgres-# union
postgres-# select id,type,date from cte where id not in (select id from cte where flag='flag_not_none'  and  (id,date) in (select id,max(date) from cte group by id)) and flag='flag_none';
id  |  type   |    date
-----+---------+------------
123 | PLASTIC | 2021-12-31
124 | NONE    | 2021-12-31
125 | STEEL   | 2021-12-31
(3 rows)