前 5 条记录，合并来自同一张表的两个查询

使用表格提供模式，然后使用窗口函数中的模式对每个模式的行进行编号。然后连接来自每个模式结果的行以配对 n = 1 个案例、n = 2 个案例等。

在以下示例中，第二个 CTE 术语仅用于提供测试数据（您的 inv_tran 表）。当实际表存在时，只需删除该 CTE 术语。如果您真的不想将每个模式组的 5 个结果配对，只需删除最后一个连接即可。

WITH patterns (pattern) AS (
        SELECT 'B%' FROM dual UNION
        SELECT 'S%' FROM dual
     )
   , inv_tran (user_id, update_qty, code, to_loc_id, work_group, dstamp) AS (
        SELECT  1, 121, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
        SELECT  2, 122, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
        SELECT  3, 123, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
        SELECT  4, 124, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
        SELECT  5, 125, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
        SELECT  6, 126, 'Pick', 'CONTAINER', 'Battery', sysdate FROM dual UNION
        SELECT 11, 121, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual UNION
        SELECT 12, 122, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual UNION
        SELECT 13, 123, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual UNION
        SELECT 14, 124, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual UNION
        SELECT 15, 125, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual UNION
        SELECT 16, 126, 'Pick', 'CONTAINER', 'Storage', sysdate FROM dual
     )
   , grps AS (
        SELECT *
          FROM (
                 SELECT user_id, SUM(update_qty)
                      , ROW_NUMBER() OVER (PARTITION BY pattern ORDER BY SUM(update_qty) DESC) AS n
                      , pattern
                   FROM inv_tran, patterns
                  WHERE code = 'Pick'
                    AND to_loc_id = 'CONTAINER'
                    AND work_group LIKE pattern
                    AND dstamp BETWEEN to_date('30/07/2021  18:00:00', 'dd/mm/yyyy hh24:mi:ss')
                                   AND to_date('31/07/2021  06:00:00', 'dd/mm/yyyy hh24:mi:ss') 
                  GROUP BY user_id, pattern
               )
         WHERE n <= 5
     )
SELECT g1.*
     , g2.*
  FROM grps g1
  JOIN grps g2
    ON g1.n = g2.n
   AND g1.pattern < g2.pattern
 ORDER BY g1.n
;

结果：