oracle读取评论列表，先按点赞次数取前三名，然后按创建时间倒序排序，需要分页：

第一种方式：

select *

from (SELECT b.*,DECODE(rownum,1,2,3,4) rank
FROM (SELECT a.*
FROM ACT_COMMENT_INFO a
order by a.prize_num desc,ref desc) b) c

order by c.rank,c.ref desc;

第二种方式：

SELECT aa.*
FROM (

SELECT *
FROM (SELECT a.*,1 o
FROM ACT_COMMENT_INFO a
WHERE a.target_act_id = 24
ORDER BY a.prize_num DESC,ref desc)
WHERE rownum <= 3
UNION
SELECT *
FROM (SELECT b.*,2 o
FROM ACT_COMMENT_INFO b
WHERE b.target_act_id = 24
ORDER BY b.ref DESC)) aa
ORDER BY aa.o,aa.ref desc;

第二种方式可能出现重复数据。

原文链接：https://www.f2er.com/oracle/211121.html