需要在Oracle PLSQL中获得适当/预期的行号

Question

当我使用以下查询

SELECT P.RefNum
    ,Ian.ID
    ,Ian.Date
    ,Igl.Name
    ,Ian.Comments
    ,ROW_NUMBER() OVER (
        PARTITION BY P.RefNum
        ,I.Name ORDER BY Ian.Name
        ) AS ROWNUMBER
FROM Table1 P
INNER JOIN Table2 Igl ON P.GrpNum = Igl.GrpNum
INNER JOIN Table3 I ON Igl.Num = I.Num
INNER JOIN Table4 Ian ON Igl.Num = Ian.Num
WHERE P.RefNum = <InputParameter>

但预期的输出应如下（请参阅 rownumber 列）

Answer 1

首先找到“组”这些行属于（由refnum和name划分）（这就是temp cte所做的），然后应用dense_rank来获取最终结果。

示例数据（简化，因为我没有您的表）：

SQL> WITH
  2     test (refnum,
  3           id,
  4           datecreated,
  5           name)
  6     AS
  7        (SELECT 3, 7000, DATE '2022-04-18', 'A-1' FROM DUAL
  8         UNION ALL
  9         SELECT 3, 7001, DATE '2022-04-19', 'A-1' FROM DUAL
 10         UNION ALL
 11         SELECT 3, 7002, DATE '2022-04-20', 'A-1' FROM DUAL
 12         UNION ALL
 13         SELECT 3, 7003, DATE '2022-03-29', '2-3' FROM DUAL
 14         UNION ALL
 15         SELECT 3, 7004, DATE '2022-03-30', '2-3' FROM DUAL
 16         UNION ALL
 17         SELECT 3, 7005, DATE '2022-04-11', 'L-5' FROM DUAL),

查询从这里开始：

 18     temp
 19     AS
 20        (SELECT t.*,
 21                id - ROW_NUMBER () OVER (PARTITION BY refnum, name ORDER BY id) grp
 22           FROM test t)
 23    SELECT t.*, DENSE_RANK () OVER (ORDER BY grp) rn
 24      FROM temp t
 25  ORDER BY refnum, name, id;

    REFNUM         ID DATECREATE NAM        GRP         RN
---------- ---------- ---------- --- ---------- ----------
         3       7000 18-04-2022 A-1       6999          1
         3       7001 19-04-2022 A-1       6999          1
         3       7002 20-04-2022 A-1       6999          1
         3       7005 11-04-2022 L-5       7004          3
         3       7003 29-03-2022 2-3       7002          2
         3       7004 30-03-2022 2-3       7002          2

6 rows selected.

SQL>