已知数据
drop table if exists test cascade;
create table test(id int, name varchar,create_time timestamp);
insert into test values(1,'a','2022-4-29 11:28:19');
insert into test values(1,'b','2022-4-30 11:28:19');
insert into test values(1,'c','2022-5-31 11:28:19');
insert into test values(2,'A','2022-4-30 11:28:19');
insert into test values(2,'B','2022-5-31 11:28:19');
insert into test values(3,'C','2022-4-30 11:28:19');
select * from test;
postgres=# select * from test;
id | name | create_time
----+------+---------------------
1 | a | 2022-04-29 11:28:19
1 | b | 2022-04-30 11:28:19
1 | c | 2022-05-31 11:28:19
2 | A | 2022-04-30 11:28:19
2 | B | 2022-05-31 11:28:19
3 | C | 2022-04-30 11:28:19
(6 rows)
期望查询的结果对应的记录
1 C '2022-5-31 11:28:19' 3
2 B '2022-5-31 11:28:19' 2
3 C '2022-4-30 11:28:19' 1
解决过程
with temp as (
select id, name, create_time, count(*) over(partition by id ) from test
) select *from temp
id | name | create_time | count
----+------+---------------------+-------
1 | a | 2022-04-29 11:28:19 | 3
1 | b | 2022-04-30 11:28:19 | 3
1 | c | 2022-05-31 11:28:19 | 3
2 | A | 2022-04-30 11:28:19 | 2
2 | B | 2022-05-31 11:28:19 | 2
3 | C | 2022-04-30 11:28:19 | 1
(6 rows)
with temp as (
select id, name, create_time, count(*) over(partition by id order by create_time) from test
) select *from temp;
id | name | create_time | count
----+------+---------------------+-------
1 | a | 2022-04-29 11:28:19 | 1
1 | b | 2022-04-30 11:28:19 | 2
1 | c | 2022-05-31 11:28:19 | 3
2 | A | 2022-04-30 11:28:19 | 1
2 | B | 2022-05-31 11:28:19 | 2
3 | C | 2022-04-30 11:28:19 | 1
with temp2 as (
with temp as (
select id, name, create_time, count(*) over(partition by id order by create_time) from test
) select id, name, create_time,count, row_number() over(partition by id order by count desc) as row_num from temp
) sellect id, name, create_time ,count from temp2 where row_num=1;
id | name | create_time | count
----+------+---------------------+-------
1 | c | 2022-05-31 11:28:19 | 3
2 | B | 2022-05-31 11:28:19 | 2
3 | C | 2022-04-30 11:28:19 | 1
(3 rows)
