Oracle SQL：先按分組排序再取各組前 5 筆的高效寫法

需求：依 ORDERED 分組，組內按 CDATE 降序，取每組前 5 筆；最終結果先按各組最大日期降序、再按分組值、組內日期排序。

核心技巧：FIRST_VALUE 窗口函數取組最大日期作為二級排序鍵

SELECT *
FROM (
    SELECT a.*,
           ROW_NUMBER() OVER (PARTITION BY ordered ORDER BY cdate DESC) AS px,
           FIRST_VALUE(cdate) OVER (
               PARTITION BY ordered ORDER BY cdate DESC
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
           ) AS ordered_date
    FROM (
        SELECT a.*, b.user_name AS username
        FROM view_hjzx a
        LEFT JOIN gjjx.sys_users b ON a.userid = b.userid
        WHERE a.HANDSET LIKE '%15901054535%'
    ) a
) 
WHERE px <= 5
ORDER BY ordered_date DESC, ordered DESC, cdate DESC;

關鍵點解析

• ROW_NUMBER() OVER (PARTITION BY ordered ORDER BY cdate DESC)：組內編號，px <= 5 即取前 5。
• FIRST_VALUE(...) OVER (PARTITION BY ordered ...)：取該組最新 cdate 作為 ordered_date，外層 ORDER BY ordered_date DESC 確保「日期最大的組在最前」，解決原寫法「第二組有更大日期卻跑到第一組前面」的問題。
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：窗口框架涵蓋全組，保證 FIRST_VALUE 穩定取到組內最大值。

此寫法單次掃描、無需自連接，效能優於多層子查詢方案。