大龙的博客

常用链接

统计

最新评论

Oracle分组排序查询

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

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


把此查询按讨论中简化一下,建一数据表 
Sql代码  收藏代码
  1. CREATE TABLE HW  
  2. (DEPID NUMBER,  
  3. DEPT VARCHAR2(30),  
  4. AMOUNT NUMBER);  



插入一些测试数据: 
Sql代码  收藏代码
  1. INSERT INTO HW VALUES(10,'北京',100);  
  2. INSERT INTO HW VALUES(10,'上海',200);  
  3. INSERT INTO HW VALUES(10,'南京',300);  
  4. INSERT INTO HW VALUES(20,'山东',400);  
  5. INSERT INTO HW VALUES(20,'河南',500);  
  6. INSERT INTO HW VALUES(20,'河北',600);  
  7. INSERT INTO HW VALUES(30,'湖南',700);  
  8. INSERT INTO HW VALUES(30,'浙江',800);  
  9. INSERT INTO HW VALUES(30,'陕西',900);  


查询要求是: 
要求用sql查询每个分组中amount最大的前两条记录 

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

不使用子查询语句为: (简单的分析函数的应用, 分析函数还包括rank()over(),dense_rank()over()等) 
Sql代码  收藏代码
  1. SELECT * FROM  
  2. (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY DEPID ORDER BY AMOUNT DESC) RN FROM HW T)  
  3. 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代码  收藏代码
  1. SELECT *  
  2. FROM HW tr  
  3. WHERE  
  4. (SELECT COUNT(*) FROM HW WHERE tr.DEPID=DEPID AND AMOUNT>tr.AMOUNT)< 2  
  5. 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 大龙 阅读(507) 评论(0)  编辑 收藏 引用


只有注册用户登录后才能发表评论。
【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理