oracle分组偏移量,将限制和偏移量添加到使用分区的Oracle查询

SELECT * FROM

(SELECT discussions.created_at, discussions.id, discussions.title, discussions.stub,

users.username AS created_by,

count(distinct threads.id) over (partition by discussions.created_at,

discussions.title,

users.username) AS replies,

count(distinct discussion_views.discussion_id)

over (partition by discussions.created_at,

discussions.title,

users.username) AS views,

latest_poster.username 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

JOIN users latest_poster ON (latest_poster.id=threads.user_id)

WHERE discussions.course_id={$course_id}

) sq

WHERE rn=1

ORDER BY created_at desc"