在子查询中选择多个值是行不通的,你需要使用标量子查询选择单个值:
WITH dataset (ID, Desc, progress, updated_time) AS (
VALUES
(1, 'abcd', 'planned', timestamp '2022-04-20 10:00'),
(1, 'abcd', 'planned', timestamp '2022-04-25 12:00'),
(1, 'abcd', 'in progress', timestamp '2022-04-26 16:00'),
(1, 'abcd', 'in progress', timestamp '2022-05-04 11:00'),
(1, 'abcd', 'in progress', timestamp '2022-05-06 12:00'),
(1, 'abcd', 'in progress', timestamp '2022-05-07 12:00'),
(2, 'abcd', 'in progress', timestamp '2022-05-04 11:00'),
(2, 'abcd', 'in progress', timestamp '2022-05-06 12:00')
)
select id, Desc, progress, updated_time
from dataset o
where updated_time = (select max(updated_time) from dataset i where i.id = o.id)
或者使用 max
窗口函数和子查询的类似方法:
select id, Desc, progress, updated_time
from (
select *, max(updated_time) over (partition by id) max_time
from dataset
)
where max_time = updated_time
或者只是使用 row_number
:
select id, Desc, progress, updated_time
from
(
select *,
row_number() over(partition by id order by updated_time desc) rank
from dataset
)
where rank = 1
输出:
编号 |
描述 |
进度 |
更新时间 |
1 |
abcd |
进行中 |
2022-05-07 12:00:00.000 |
2 |
abcd |
进行中 |
2022-05-06 12:00:00.000 |