create table hw
(depid number,
dept varchar2(30),
amount number);

insert into hw
values(10,'北京',100);
insert into hw
values(10,'上海',200);
insert into hw
values(10,'南京',300);
insert into hw
values(20,'山东',400);
insert into hw
values(20,'河南',500);
insert into hw
values(20,'河北',600);
insert into hw
values(30,'湖南',700);
insert into hw
values(30,'浙江',800);
insert into hw
values(30,'陕西',900);
要求用sql查询每个分组中amount最大的前两条记录
即
depid      dept       amount                                            depid      dept       amount
10         上海        200                                                    10         南京        300
10         南京        300                                                    10         上海        200
20         河南        500                 或者 ===》                  20         河北        600
20         河北        600                                                    20         河南        500
30         浙江        800                                                    30         陕西        900
30         陕西        900                                                    30         浙江        800

请大家注意，depid 可能有很多，所以表中的记录也许不止现在的这么几条。
再次谢谢大家。

这有何难：

PHP code:



SQL> select * from hw;


 


     DEPID DEPT                               AMOUNT


---------- ------------------------------ ----------


        10 北京                                  100


        10 上海                                  200


        10 南京                                  300


        20 山东                                  400


        20 河南                                  500


        20 河北                                  600


        30 湖南                                  700


        30 浙江                                  800


        30 陕西                                  900


 


9 rows selected


 


SQL> 


SQL> select *


  2    from (select t.*,


  3                 row_number() over(partition by depid order by amount desc) rn


  4            from hw t)


  5   where rn < 3


  6  /


 


     DEPID DEPT                               AMOUNT         RN


---------- ------------------------------ ---------- ----------


        10 南京                                  300          1


        10 上海                                  200          2


        20 河北                                  600          1


        20 河南                                  500          2


        30 陕西                                  900          1


        30 浙江                                  800          2


 


6 rows selected


 


SQL> 


--

仅供参考：
SELECT x.depid,x.dept,x.amount FROM
  (SELECT ROWNUM listid,a.* FROM
    (SELECT * FROM hw ORDER BY depid,amount DESC) a
  ) x
  ,(SELECT depid,MIN(ROWNUM) listid FROM(
    SELECT ROWNUM,a.* FROM (
      SELECT * FROM hw a ORDER BY depid,amount DESC) a
      )
    GROUP BY depid
  ) y
WHERE x.depid=y.depid AND x.listid<=y.listid+1
  ORDER BY x.depid,x.amount;

用rank()over(),dense_rank()over() 和上面（2楼）那个人的分析函数都可以。

junsansi好帅啊。

受教。

QUOTE:

---

原帖由 stand32 于 2008-8-7 10:44 发表
仅供参考：
SELECT x.depid,x.dept,x.amount FROM
  (SELECT ROWNUM listid,a.* FROM
    (SELECT * FROM hw ORDER BY depid,amount DESC) a
  ) x
  ,(SELECT depid,MIN(ROWNUM) listid FROM(
    SELECT ROWNUM,a.* FROM (
      SELECT * FROM hw a ORDER BY depid,amount DESC) a
      )
    GROUP BY depid
  ) y
WHERE x.depid=y.depid AND x.listid

---

这个对表进行全表扫描了两次，比三思兄的那段性能低了一倍左右。

呵呵，我没有学过分析函数，只能拿我可以利用的知识处理了，不好意思。

QUOTE:

---

原帖由 junsansi 于 2008-8-7 10:32 发表
这有何难：

PHP code:



SQL> select * from hw;


 


     DEPID DEPT                               AMOUNT


---------- ------------------------------ ----------


        10 北京                                  100


        10 上海                                  200


        10 南京                                  300


        20 山东                                  400


        20 河南                                  500


        20 河北                                  600


        30 湖南                                  700


        30 浙江                                  800


        30 陕西                                  900


 


9 rows selected


 


SQL> 


SQL> select *


  2    from (select t.*,


  3                 row_number() over(partition by depid order by amount desc) rn


  4            from hw t)


  5   where rn < 3


  6  /


 


     DEPID DEPT                               AMOUNT         RN


---------- ------------------------------ ---------- ----------


        10 南京                                  300          1


        10 上海                                  200          2


        20 河北                                  600          1


        20 河南                                  500          2


        30 陕西                                  900          1


        30 浙江                                  800          2


 


6 rows selected


 


SQL> 


--

---

还是JUNSANSI的方法好

select x.depid,x.dept,x.amount
    from (select t.*,
                 row_number() over(partition by depid order by amount desc) rn
              from zt t) x
    where rn < 3
DEPID DEPT                           AMOUNT
----- ------------------------------ ------
   10 南京                              300
   10 上海                              200
   20 河北                              600
   20 河南                              500
   30 陕西                              900
   30 浙江                              800
6 rows selected