SQL> select sal,job from emp;       SAL JOB
---------- ---------
       800 CLERK
      1600 SALESMAN
      1250 SALESMAN
      2975 MANAGER
      1250 SALESMAN
      2850 MANAGER
      2450 MANAGER
      3000 ANALYST
      5000 PRESIDENT
      1500 SALESMAN
      1100 CLERK       SAL JOB
---------- ---------
       950 CLERK
      3000 ANALYST
      1300 CLERK已选择14行。
SQL> select job,sal,rank() over(partition by job order by sal) from emp;JOB              SAL RANK()OVER(PARTITIONBYJOBORDERBYSAL)
--------- ---------- ------------------------------------
ANALYST         3000                                    1
ANALYST         3000                                    1
CLERK            800                                    1
CLERK            950                                    2
CLERK           1100                                    3
CLERK           1300                                    4
MANAGER         2450                                    1
MANAGER         2850                                    2
MANAGER         2975                                    3
PRESIDENT       5000                                    1
SALESMAN        1250                                    1JOB              SAL RANK()OVER(PARTITIONBYJOBORDERBYSAL)
--------- ---------- ------------------------------------
SALESMAN        1250                                    1
SALESMAN        1500                                    3
SALESMAN        1600                                    4已选择14行。SQL>

解决方案 »

  1. select cola,colb,count(cola) over (partition by cola order by colb) from t2
    COLA       COLB       COUNT(COLA)OVER(PARTITIONBYCOLAORDERBYCOLB)
    ---------- ---------- -------------------------------------------
    a          d                                                    1
    a          g                                                    2
    a          q                                                    3
    b          f                                                    1
    b          gh                                                   2
    b          rt                                                   3
    c          er                                                   1
    c          rt                                                   2已选择8行。
      

  2. 8.0.5下面好象是不能用rank() over(partition by 的,
      

  3. SQL> select * from t;AA         BB
    ---------- ----------
    A          aa
    A          bb
    B          b
    A          b
    B          ccSQL> select maxid-id+1,tt.aa,tt.bb from (
      2  select max(id) maxid,aa from (
      3  select rownum id,aa,bb from (
      4  select * from t order by aa)) group by aa
      5  ) t,
      6  (select rownum id,aa,bb from (
      7  select * from t order by aa)) tt
      8  where tt.aa=t.aa(+);MAXID-ID+1 AA         BB
    ---------- ---------- ----------
             3 A          aa
             2 A          bb
             1 A          b
             2 B          b
             1 B          ccSQL>
      

  4. 佩服.
    感谢 bzszp(www.bzszp.533.net)的关心:开拓了思路.
    实际答案:
     select  tt.cola, tt.colb, id-minid+1
         from (select cola,min(id) minid
                 from (select rownum id, cola, colb
                         from (select * from t2_fenzu order by cola))
                group by cola) t,
              (select rownum id, cola, colb
                 from (select * from t2_fenzu order by cola))   tt
        where t.cola = tt.cola
      

类似问题 »