Oracle_分组排序 zr.wangweiqing@huawei.com

江841

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



-----查询 按照 DEPID 分组，每个DEPID分组中 AMOUNT 最高的 前 2 名

方法一：使用分析函数查询

select * from (

select t.* ,rank() over(partition by depid order by amount desc) rn from hw t )

where rn <3;



方法二：使用子查询的语句为：

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


rank、dense_rank、row_number ：

1：RANK():跳跃排序，如果有两个第二名，接下来的就是第四名，而不是三名

2：DENSE_RANK():连续排序，如果有两个第二，接下来的仍是三

3：ROW_BUMBER():序号进行递增