JdbcTemplate选择查询以获取唯一的行（每个具有唯一ID的行只存在一次）

Question

有人愿意帮我完成一个jdbctemplate查询吗？

只需要获取具有唯一id的行，但存在重复的行，因为其他列（如date）具有不同的值。我需要最好得到最长日期，结果集不应该有任何重复项：x个

select files.id, files.popularity, user_clicked_files.last_clicked from files inner join user_clicked_files on files.id = user_clicked_files.file_id where user_clicked_files.last_clicked > ? order by files.popularity desc limit 10

输出：

[File [id=1a9227b2-d337-4c4b-a26c-42ed8c94de34, last_clicked='2022-05-30', popularity='8'], 
File [id=1a9227b2-d337-4c4b-a26c-42ed8c94de34, last_clicked='2022-06-03', popularity='8'], 
File [id=61f3860c-22b3-4c24-90bd-98c7f520fad7, last_clicked='2022-06-04', popularity='8'], 
File [id=61f3860c-22b3-4c24-90bd-98c7f520fad7, last_clicked='2022-06-03', popularity='8'], 
File [id=d70ff033-04cb-4205-acfe-2432f66525c2, last_clicked='2022-05-30', popularity='7'], 
File [id=d70ff033-04cb-4205-acfe-2432f66525c2, last_clicked='2022-05-30', popularity='7'], 
File [id=d70ff033-04cb-4205-acfe-2432f66525c2, last_clicked='2022-05-31', popularity='7'], 
File [id=9543b842-d592-46df-a63c-8e7c14791169, last_clicked='2022-06-04', popularity='7'], 
File [id=d70ff033-04cb-4205-acfe-2432f66525c2, last_clicked='2022-05-29', popularity='7'], 
File [id=d70ff033-04cb-4205-acfe-2432f66525c2, last_clicked='2022-06-04', popularity='7']]

这几乎有效，但不完全有效。可悲的是，有重复的。

这是我正在使用的两张桌子。

CREATE TABLE files
(
    id         uuid DEFAULT gen_random_uuid() not null primary key,
    file_name  VARCHAR(255),
    popularity INTEGER
);
CREATE TABLE user_clicked_files
(
    user_id      uuid,
    file_id      uuid,
    last_clicked date,
    PRIMARY KEY (user_id, file_id)
);

PS.：使用PostgreSQL

Answer 1

想出来了。这是查询。

select f.id, f.popularity, x.last_clicked
from files f
join (
  select file_id, max(last_clicked) as last_clicked
  from user_clicked_files
  where last_clicked > ?
  group by file_id) x
on (f.id=x.file_id)
order by f.popularity desc
limit 10

Answer 2

您可以使用带有如下常见表表达式的窗口函数来选择每个id最近单击的行：

with cte as
(
  select files.id, files.popularity, user_clicked_files.last_clicked ,ROW_NUMBER()over(partition by files.id order by user_clicked_files.last_clicked desc)rn
  from files 
  inner join user_clicked_files on files.id = user_clicked_files.file_id 
  where user_clicked_files.last_clicked > ? 
)
select id, popularity, last_clicked from cte where rn=1
order by files.popularity desc limit 10