Oracle_分组排序

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():序号进行递增