以前一直没有在partition by中使用过case when,刚才试了一下,也算是个小技巧吧。
SQL> select * from t1;
ID
----------
1
2
1
2
3
4
6 rows selected.
SQL> select t1.*,row_number() over(partition by id order by id) as rn from t1; --不加case when的时候
ID RN
---------- ----------
1 1
1 2
2 1
2 2
3 1
4 1
6 rows selected.
SQL> select t1.*,row_number() over(
2 partition by case when id=1 then 1
3 when id=2 then 2
4 else 3 end order by id) as rn from t1;
ID RN
---------- ----------
1 1 --id=1为一个分组
1 2