查询找到至少第五名受薪员工[重复]

This question already has an answer here:

这个问题在这里已有答案:

• Select second most minimum value in Oracle 3 answers

在Oracle 3答案中选择第二个最小值

EMPID NAME DEPTID SALARY
---------- ------------------------------------------ --
101 surendra 201 1000
102 narendra 202 2000
103 rajesh 203 3000
104 ramesh 203 2000
105 hanumanth 202 10000

a) Write a Query to find least 5th (Least salary in 5th position from least salary in the order) salaried employee?

a)写一个查询,找到至少第5名(从订单中的最低工资中获得第5名的最低工资)受薪员工?

b) Query to find the highest earning employee in each department

b)查询以查找每个部门中收入最高的员工

2 个解决方案

#1

a) Write a Query to find least 5th (Least salary in 5th position from least salary in the order) salaried employee?

a)写一个查询,找到至少第5名(从订单中的最低工资中获得第5名的最低工资)受薪员工?

SELECT EMPID,NAME,DEPTID,SALARY
FROM
(
SELECT EMPID,NAME,DEPTID,SALARY, DENSE_RANK() OVER (ORDER BY SALARY) AS RN
FROM Table1
) 
WHERE RN=5

b) Query to find the highest earning employee in each department

b)查询以查找每个部门中收入最高的员工

SELECT EMPID,NAME,DEPTID,SALARY
FROM
(
SELECT EMPID,NAME,DEPTID,SALARY, 
  DENSE_RANK() OVER (PARTITION BY DEPTID ORDER BY SALARY DESC) AS RN
FROM Table1
)
WHERE RN=1

Demo

http://sqlfiddle.com/#!4/63ce0/12

#2

Let's assume we have a table named EmployeeSalary which has 3 columns:

假设我们有一个名为EmployeeSalary的表,它有3列:

1. Id
2. Name
3. Salary

Salary might be same for multiple employee according to the following table data

根据以下表格数据,多名员工的薪水可能相同

Id  Name    Salary
----------
6   Belalo  74
1   Karim   100
5   dIPU    100
4   Satter  102
9   Kiron   120
10  Rash    120
11  Harun   130
13  Baki    130
12  Munshi  132
2   Rahim   500
7   Kaif    987
8   Sony    987
3   Belal   4000

Now the query will be

现在查询将是

SELECT A.Salary
FROM
(
SELECT DISTINCT Salary, 
DENSE_RANK() OVER (ORDER BY Salary) AS Ranks
FROM [dbo].[EmployeeSalary]
) A 
WHERE A.Ranks=5