去重复

最新推荐文章于 2023-12-13 10:55:27 发布

原创最新推荐文章于 2023-12-13 10:55:27 发布 · 572 阅读

1 ·

CC 4.0 BY-SA版权

Oracle Database 专栏收录该内容

36 篇文章

订阅专栏

create table eric_employee (empid int ,deptid int ,salary decimal(10,2));

insert into eric_employee values(1,10,5500.00);
insert into eric_employee values(2,10,4500.00);
insert into eric_employee values(3,20,1900.00);
insert into eric_employee values(4,20,4800.00);
insert into eric_employee values(5,40,6500.00);
insert into eric_employee values(6,40,14500.00);
insert into eric_employee values(7,40,44500.00);
insert into eric_employee values(8,50,6500.00);
insert into eric_employee values(9,50,7500.00);

SELECT deptid
FROM eric_employee
WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM eric_employee GROUP BY deptid);

SELECT *
FROM (SELECT deptid,
row_number() over(PARTITION BY deptid ORDER BY salary) row_id
FROM eric_employee) eric
WHERE eric.row_id = 1;

SELECT deptid,
ROWID,
row_number() over(PARTITION BY deptid ORDER BY salary) row_id
FROM eric_employee;