高分急求，在线等

Question

如果编号类型固定的话
只能达到这种效果
09:36:22 SQL> select * from t;COL1                COL2             COL3
------------------- ---------- ----------
2003-01-01 00:00:00 A                  34
2003-01-01 00:00:00 B                  45
2003-01-01 00:00:00 C                  23
2003-01-02 00:00:00 B                  34
2003-01-02 00:00:00 C                  45
2003-01-03 00:00:00 A                  31
2003-01-03 00:00:00 C                  56
2003-01-04 00:00:00 B                  12
2003-01-04 00:00:00 A                  34
2003-01-04 00:00:00 C                  67
2003-01-04 00:00:00 D                  78COL1                COL2             COL3
------------------- ---------- ----------
2003-01-05 00:00:00 A                  34
2003-01-05 00:00:00 C                  23已选择13行。已用时间:  00: 00: 00.63
09:36:33 SQL> select rk,sum(decode(col2,'A',cs,0)) A,
09:36:40   2  sum(decode(col2,'B',cs,0)) B,
09:36:40   3  sum(decode(col2,'C',cs,0)) C,
09:36:40   4  sum(decode(col2,'D',cs,0)) D
09:36:40   5  from (
09:36:40   6  select col2,rk,count(*) cs from (
09:36:40   7  select col2,col3,rank() over(partition by col1 order by col3) rk
09:36:40   8  from t) tb group by col2,rk
09:36:40   9  ) ttb group by rk;        RK          A          B          C          D
---------- ---------- ---------- ---------- ----------
         1          1          2          2          0
         2          3          0          2          0
         3          0          1          1          0
         4          0          0          0          1已用时间:  00: 00: 00.16
09:36:40 SQL>

Answer 1

如果编号类型不固定的话
只能做到这样了
09:36:40 SQL> select col2,rk,count(*) cs from (
09:37:58   2  select col2,col3,rank() over(partition by col1 order by col3) rk
09:37:58   3  from t) tb group by col2,rk;COL2               RK         CS
---------- ---------- ----------
A                   1          1
A                   2          3
B                   1          2
B                   3          1
C                   1          2
C                   2          2
C                   3          1
D                   4          1已选择8行。已用时间:  00: 00: 00.32
09:38:00 SQL>

Answer 2

SQL> desc tab4;
 名称                                      空值?    类型
 ----------------------------------------- -------- ---------------------------- RQ                                                 DATE
 NUM                                                VARCHAR2(1 CHAR)
 AMOUNT                                             NUMBER(2)SQL> select * from tab4;RQ         NU     AMOUNT
---------- -- ----------
01-1月 -03 A          34
01-1月 -03 B          45
01-1月 -03 C          23
02-1月 -03 B          34
02-1月 -03 C          45
03-1月 -03 A          31
03-1月 -03 C          56
04-1月 -03 B          12
04-1月 -03 A          34
04-1月 -03 C          67
04-1月 -03 D          78
RQ         NU     AMOUNT
---------- -- ----------
05-1月 -03 A          34
05-1月 -03 C          23已選取 13 個資料列.SQL> SELECT num||'->'||to_char(count) FROM (
        select num,count(num) count FROM (
        SELECT * FROM (
        SELECT t.RQ,t.NUM,DENSE_RANK() OVER(PARTITION BY RQ ORDER BY  AMOUNT DESC)
        RANK FROM TAB4 t) 
        WHERE RANK=1) group by num order by count desc);NUM||'->'||TO_CHAR(COUNT)
--------------------------------------------------------------------------------C->2
A->1
D->1
B->1SQL> SELECT num||'->'||to_char(count) FROM (
        select num,count(num) count FROM (
        SELECT * FROM (
        SELECT t.RQ,t.NUM,DENSE_RANK() OVER(PARTITION BY RQ ORDER BY  AMOUNT DESC)
        RANK FROM TAB4 t) 
        WHERE RANK=2) group by num order by count desc);
NUM||'->'||TO_CHAR(COUNT)
--------------------------------------------------------------------------------A->2
C->2
B->1SQL> SELECT num||'->'||to_char(count) FROM (
        select num,count(num) count FROM (
        SELECT * FROM (
        SELECT t.RQ,t.NUM,DENSE_RANK() OVER(PARTITION BY RQ ORDER BY  AMOUNT DESC)
        RANK FROM TAB4 t) 
        WHERE RANK=3) group by num order by count desc);NUM||'->'||TO_CHAR(COUNT)
--------------------------------------------------------------------------------A->1
C->1SQL> SELECT num||'->'||to_char(count) FROM (
        select num,count(num) count FROM (
        SELECT * FROM (
        SELECT t.RQ,t.NUM,DENSE_RANK() OVER(PARTITION BY RQ ORDER BY  AMOUNT DESC)
        RANK FROM TAB4 t) 
        WHERE RANK=4) group by num order by count desc);NUM||'->'||TO_CHAR(COUNT)
--------------------------------------------------------------------------------B->1应该可以将上面的sql应用到存储过程使得rank可以等于一个动态的值。