求SQL,分组统计

Question

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>

Answer 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行。

Answer 2

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

Answer 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>

Answer 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