我正在为一个应用程序构建一个论坛,我一直在努力获取讨论的最新海报。我使用的数据库是Oracle。
我有以下表格:
discussions
id
course_id
user_id
title
stub
created_at
threads
id
discussion_id
user_id
created_at
updated_at
message
discussion_views
discussion_id
user_id
time
users
id
username
我有以下查询:
select discussions.created_at,
discussions.title,
users.username,
count(threads.id) AS "replies",
count(distinct discussion_views.discussion_id) AS "views"
from discussions
left join threads on discussions.id=threads.discussion_id
left join discussion_views on discussions.id=discussion_views.discussion_id
join users on users.id=discussions.user_id
group by discussions.created_at, discussions.title, users.username
order by discussions.created_at desc
我需要的是得到最后一个在帖子中发帖的用户和日期。我应该做一个内部插入,一个连接,还是应该做另一个插入。我也希望我的查询是有性能的,因为我的论坛需要处理大量的流量。
尝试:
select * from
(select discussions.created_at,
discussions.title,
users.username as discussion_created_by,
count(distinct threads.id) over (partition by discussions.created_at,
discussions.title,
users.username) AS replies,
count(distinct discussion_views.time)
over (partition by discussions.created_at,
discussions.title,
users.username) AS "views",
threads.user_id AS latest_post_by,
threads.updated_at AS latest_post_at,
row_number() over (partition by discussions.created_at,
discussions.title,
users.username
order by threads.id desc) AS rn
from discussions
left join threads on discussions.id=threads.discussion_id
left join discussion_views on discussions.id=discussion_views.discussion_id
join users on users.id=discussions.user_id) sq
where rn=1
order by created_at desc