Oracle分组排序查询

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

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

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


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

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);

[b]查询要求是:[/b]
要求用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自身的查询方法，效率会高些，但是给移稙带来不利影响
子查询的优点是可用于任何数据库，但是效率差些