ORACLE分析函数over(partition by...)中使用case when达到多重分组的效果

以前一直没有在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
         2          1  --id=2为一个分组
         2          2
         3          1  --其余的id值为一个分组
         4          2

6 rows selected.

SQL> select t1.*,row_number() over(
  2  partition by case when id=1 then 1
  3  else 2 end order by id) as rn from t1;

        ID         RN
---------- ----------
         1          1 --id为1的值作为一个分组
         1          2
         2          1 --id除了1以外的值当作一个分组
         2          2
         3          3
         4          4

6 rows selected.

 

这里在case when中使用的是等于号，你也可以在case when中指定其他任何不同的条件表达式，可以达到你想要的特定的分组效果。