大龙的博客

常用链接

统计

随笔 - 864
文章 - 2
评论 - 378
引用 - 0

Oracle分组排序查询

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

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

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

Sql代码  
CREATE TABLE HW  
(DEPID NUMBER,  
DEPT VARCHAR2(30),  
AMOUNT NUMBER);  

插入一些测试数据:

Sql代码  
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()等)

Sql代码  
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

使用子查询的语句为：

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

posted on 2011-09-16 16:44 大龙阅读(538) 评论(0) 编辑收藏引用

只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻 BlogJava 知识库博问管理

大龙的博客

导航

留言簿(43)

收藏夹

随笔档案

文章档案

阅读排行榜

评论排行榜

常用链接

统计

最新评论

Oracle分组排序查询