Oracle SQL找到5个最低薪水 [英] Oracle SQL Finding the 5 lowest salaries

问题描述

我正在尝试回答以下问题.显示ID_Number和五个最低薪水雇员的姓名.

I am trying to answer the following question. Show ID_Number and name for the five lowest paid employees.

这是员工表:

CREATE TABLE Employees
    (ID_No          CHAR(4)     NOT NULL,
     Name           VARCHAR(50) NOT NULL,
     Hire_Date      DATE        NOT NULL,
     Position       VARCHAR(20) CHECK(Position IN('CHAIRMAN','MANAGER','ANALYST','DESIGNER','PROGRAMMER','SALES REP','ADMIN','ACCOUNTANT')),
     Salary         NUMERIC(8,2)    NOT NULL,
     Mgr_ID_No      CHAR(4)     NULL,
     Dept_No        SMALLINT    NULL);

我要补充一点，我一直在尝试一些方法，由于某些原因，"limit"和"top"无效.

I will add that I've been trying a few methods and "limit" and "top" do not work for some reason.

推荐答案

在 Oracle 12c 中:

-- more than 5 rows being returned, if multiple rows 
-- match the value of the 5th row
SELECT e.ID_No, e.Name
  FROM Employees e
 ORDER BY e.Salary  
 FETCH FIRST 5 ROWS WITH TIES;

-- only 5 rows being returned, even if multiple rows 
-- match the value of the 5th row
SELECT e.ID_No, e.Name
  FROM Employees e
 ORDER BY e.Salary  
 FETCH FIRST 5 ROWS ONLY; 

-- NEXT clause may be replaced with FIRST  
SELECT e.ID_No, e.Name
  FROM Employees e
 ORDER BY e.Salary 
 FETCH NEXT 5 ROWS ONLY; 

Oracle 12c 之前的版本:

SELECT e.ID_No, e.Name
  FROM ( SELECT ID_No, Name, row_number() over (order by salary) seq FROM Employees ) e
 WHERE e.seq <= 5
 ORDER BY e.seq; 

查询可用于 前N个查询

这篇关于Oracle SQL找到5个最低薪水的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！