Oracle-window函数中没有分区,但为acd属性填充序列号

您需要嵌套的OLAP功能:

SELECT dt.*,
   Row_Number() Over (PARTITION BY grp ORDER BY id)
FROM
 ( -- calculate a group number using a Cumulative Sum over 0/1 (for partitioning in next step)
   SELECT prop.*, Sum(rpt) Over (ORDER BY id ROWS Unbounded Preceding) AS grp
   FROM prop
 ) dt

什么时候? rpt = 1 然后你想要 1 . 那么你想要 0 为每个枚举的 acd . 如果这是正确的,那么逻辑是:

select t.*,
       (case when rpt = 1 then 1
             else 1 + row_number() over (partition by acd, rpt order by id)
        end) as wanted
from t;