这是某公司一道面试题.比较典型,我发出我的做法,有其它做法的可以后面回贴.

create table myorders (area_id number,order_name   varchar2(5), cnt number);

insert into myorders values (1,'A1',1);
insert into myorders values (1,'A2',10);
insert into myorders values (1,'A2',3);
insert into myorders values (1,'A3',4);
insert into myorders values (1,'A4',5);

insert into myorders values (2,'B1',10);
insert into myorders values (2,'B1',44);
insert into myorders values (2,'B2',55);
insert into myorders values (2,'B2',5);
insert into myorders values (2,'B3',2);

insert into myorders values (3,'C1',444);
insert into myorders values (3,'C2',3);
insert into myorders values (3,'C3',6);
insert into myorders values (3,'C4',10);
insert into myorders values (3,'C5',88);

   
select *
  from (select rank() over(partition by m.area_id order by m.sumcnt desc) rk,
               m.*
          from (select area_id, order_name, sum(cnt) sumcnt
                  from myorders
                group by area_id, order_name) m) t 　　where t.rk <= 3;

返回结果集如下: 

       RK    AREA_ID ORDER_NAME     SUMCNT
---------- ---------- ---------- ----------
         1          1 A2                 13
         2          1 A4                  5
         3          1 A3                  4
         1          2 B2                 60
         2          2 B1                 54
         3          2 B3                  2
         1          3 C1                444
         2          3 C5                 88
         3          3 C4                 10

9 rows selected