ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

原创转载请注明出处:http://agilestyle.iteye.com/blog/2357284

 

ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

create table

CREATE TABLE EMPLOYEE
(
  EMPID INTEGER PRIMARY KEY,
  DEPTID INTEGER,
  SALARY DECIMAL(10,2)
);

 

insert data

INSERT INTO EMPLOYEE VALUES(1, 10, 5500.00);
INSERT INTO EMPLOYEE VALUES(2, 10, 4500.00);
INSERT INTO EMPLOYEE VALUES(3, 10, 6500.00);
INSERT INTO EMPLOYEE VALUES(4, 20, 1900.00);
INSERT INTO EMPLOYEE VALUES(5, 20, 4800.00);
INSERT INTO EMPLOYEE VALUES(6, 20, 5800.00);
INSERT INTO EMPLOYEE VALUES(7, 40, 6500.00);
INSERT INTO EMPLOYEE VALUES(8, 40, 14500.00);
INSERT INTO EMPLOYEE VALUES(9, 40, 44500.00);
INSERT INTO EMPLOYEE VALUES(10, 50, 6500.00);
INSERT INTO EMPLOYEE VALUES(11, 50, 7500.00);
INSERT INTO EMPLOYEE VALUES(12, 50, 8500.00);

 

简单查询

SELECT * FROM EMPLOYEE;


 

分组排序查询

SELECT EMPID, DEPTID, SALARY, 
  ROW_NUMBER() OVER (PARTITION BY DEPTID ORDER BY SALARY DESC) rn
FROM EMPLOYEE;


 

Reference

https://docs.oracle.com/database/121/SQLRF/functions170.htm#SQLRF06100

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值