postgresql - Postgres 选择 'at least' 项

Question

我想选择要发布的评论，先于特定的 commentId，但无论如何我都希望在结果中有至少 5 条评论。

所以如果少于 5 条评论是 sql:SELECT * FROM comments WHERE id >= :comment_id，我必须再做一次选择 SELECT * FROM comments LIMIT 5.

是否可以在一个请求中得到相同的逻辑？

Answer 1

with c as (
    select count(*) as c
    from comments
    where id >= :comment_id
)
select *
from comments
where id >= :comment_id
union all
(
    select *
    from comments
    where id < :comment_id
    order by id desc
    limit greatest(5 - (select c from c), 0)
)
;