Oracle分组排序查询

原讨论链接: http://www.itpub.net/thread-1034530-1-1.html

最近遇到一场景：
查询所有账户在某天的最新修改的记录

其中，某些账户在同一天内可能不止修改一次。


把此查询按讨论中简化一下，建一数据表

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最大的前两条记录

这里提供两种查询：一种为不用子查询的方法；另一种是使用子查询的方法

不使用子查询语句为： (简单的分析函数的应用, 分析函数还包括rank()over(),dense_rank()over()等)

SELECT * FROM
(SELECT T.*, ROW_NUMBER() OVER(PARTITION BY DEPID ORDER BY AMOUNT DESC) RN FROM HW T)
WHERE RN < 3

查询的结果是:
     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



使用子查询的语句为：

SELECT *
FROM HW tr
WHERE
(SELECT COUNT(*) FROM HW WHERE tr.DEPID=DEPID AND AMOUNT>tr.AMOUNT)< 2
ORDER BY DEPID, TR.AMOUNT DESC

查询结果是：
     DEPID DEPT                               AMOUNT
---------- ------------------------------ ----------
        10 南京                                  300
        10 上海                                  200
        20 河北                                  600
        20 河南                                  500
        30 陕西                                  900
        30 浙江                                  800

6 rows selected


两种查询各有优缺点：
不用子查询的方法利用了Oracle自身的查询方法，效率会高些，但是给移稙带来不利影响
子查询的优点是可用于任何数据库，但是效率差些

评论

1 楼 bureaucrat 2011-04-13  

ROW_NUMBER()是sql:2003标准