如何从工资表中找到第三或第n个最高工资?

hea 发布于 2019-03-12 sql 最后更新 2019-03-12 09:21 148 浏览

如何以优化的方式从工资table(EmpID,EmpName,EmpSalary)中找到third or nth最高工资?

已邀请:

quia_a

赞同来自:

select min(salary) 
from (select salary 
      from employee 
      where rownum < n+1 
      order by salary desc);

det

赞同来自:

Select TOP 1 Salary as '3rd Highest Salary' from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC) a ORDER BY Salary ASC;
我的薪水排名第三

cut

赞同来自:

在2008年,我们可以使用ROW_NUMBER()OVER(ORDER BY EmpSalary DESC)获得一个没有我们可以使用的关系的排名。 例如,我们可以通过这种方式获得第8高,或者将@N更改为其他内容,或者如果您愿意,可以将其用作函数中的参数。

DECLARE @N INT = 8;
WITH rankedSalaries AS
(
SELECT
EmpID
,EmpName
,EmpSalary,
,RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
FROM salary
)
SELECT
EmpID
,EmpName
,EmpSalary
FROM rankedSalaries
WHERE RN = @N;
在SQL Server 2012中,您可能知道使用LAG()更直观地执行此操作。

sit_id

赞同来自:

SELECT TOP 1 salary FROM ( SELECT TOP n salary FROM employees ORDER BY salary DESC Group By salary ) AS emp ORDER BY salary ASC
(其中n代表第n个最高薪水)

stotam

赞同来自:

获得第n个最高薪水,请参阅以下查询。通过这种方式,你可以获得MYSQL中第n个最高薪水。如果你想获得第n个最低工资,你需要在查询中用ASC替换DESC。

nth highest salary

jdolor

赞同来自:

对于第n个最高值

select min(salary) 
from (select salary from(select salary from employee order by salary desc)where rownum<=n);

ad_est

赞同来自:

SELECT Salary,EmpName
FROM
(
SELECT Salary,EmpName,DENSE_RANK() OVER(ORDER BY Salary DESC) Rno from EMPLOYEE
) tbl
WHERE Rno=3

beos

赞同来自:

您好再使用公用表表达式编写此示例:

 with cte as
(SELECT TOP 3 salary 
   FROM salary
   ORDER BY salary DESC)
   select top 1 salary from cte
with cte as
(select e.Name,s.salary ,DENSE_RANK ()over ( order by salary desc) sal_rank 
from salary s left join employee e
on s.EmpID=e.EmpID)
select Name,salary from cte where sal_rank=3

cut

赞同来自:

set @n = $n
SELECT a.* FROM ( select a.* , @rn = @rn+1  from EMPLOYEE order by a.EmpSalary desc ) As a  where rn = @n

reos

赞同来自:

优化方式:只使用限制而不是子查询。

select distinct salary from employee order by salary desc limit nth, 1;
请参阅此处限制语法http://www.mysqltutorial.org/mysql-limit.aspx

qnon

赞同来自:

如果你想要优化方式意味着使用TOP关键字,那么第n个最大和最小工资查询如下: N最低工资:

SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary DESC) 
例如:3最低工资:
SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary DESC) 
N最高工资:
SELECT MAX(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary ASC)
对于Ex:3最高薪水:
SELECT MAX(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary ASC)

krerum

赞同来自:

你可以试试这个:

select top(1) EXPORT_NO
from DC_HDR 
order by CASE when  (ROW_NUMBER() over(order by EXPORT_NO desc))=3 then EXPORT_NO else 0 end desc

gest

赞同来自:

通过子查询:

SELECT salary from
(SELECT rownum ID, EmpSalary salary from
(SELECT DISTINCT EmpSalary from salary_table order by EmpSalary DESC)
where ID = nth)

vsit

赞同来自:

方法1:

SELECT TOP 1 salary FROM (
SELECT TOP 3 salary 
 FROM employees 
  ORDER BY salary DESC) AS emp 
 ORDER BY salary ASC
方法2:
  Select EmpName,salary from
  (
    select EmpName,salary ,Row_Number() over(order by salary desc) as rowid      
     from EmpTbl)
   as a where rowid=3

eea

赞同来自:

//你可以从table中找到第n个工资。如果你想要找到第二高的工资然后把n = 2,如果第三个hs那么很快就会得到n = 3。 PLACEHOLDER FOR_CODE_0

ksequi

赞同来自:

显示所有第三高薪:

select * from emp where sal=
(SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3,1) ;
仅显示第三高薪:
SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3,1

aad

赞同来自:

declare @nHighestSalary as int
set @nHighestSalary = 3
SELECT TOP 1 salary FROM (
   SELECT TOP @nHighestSalary salary 
   FROM employees 
   ORDER BY salary DESC) AS emp 
ORDER BY salary ASC

rsaepe

赞同来自:

优化方式,

;With CTE as
(
select salary 
        ,RANK(order by salary desc) as Rnk
from table
)
select * from CTE where Rnk = @yourVariable
第二个意见,
Declare @yourVariable int = 2
Select top 1 * from 
(
select distinct top(@yourVariable)  salary 
from Employees
order by salary desc
)as a
order by a.salary asc
select * from Employees
order by salary desc

oqui

赞同来自:

第n个最高薪水..

SELECT DISTINCT Salary
FROM EMP E WHERE
n =(SELECT COUNT(DISTINCT SALARY) 
FROM EMP WHERE E.SALARY <= SALARY)
n是你想要的最高值,即2,3等

uaut

赞同来自:

select  a.GRN_NAME 
from GRN_HDR a,GRN_HDR b 
where a.GRN_NAME<=b.GRN_NAME 
group by a.GRN_NAME 
having count(a.GRN_NAME)=3

fearum

赞同来自:

这是用于从表emp中获取第n个最高薪水的mysql查询。 我们在这里使用“LIMIT”作为“TOP”的替代,参数(n-1)显示从哪一行开始,第二个参数显示从第(n-1)行开始显示的行数。 “LIMIT”也接受单个参数,该参数表示从第0(或第1)开始显示的行数。

select distinct * from emp order by sal desc limit (n-1),1;

zcum

赞同来自:

试试这个查询

SELECT DISTINCT salary
FROM emp E WHERE
&no =(SELECT COUNT(DISTINCT salary) 
FROM emp WHERE E.salary <= salary)
把n =你想要的值

zamet

赞同来自:

SELECT EmpSalary FROM salary_table GROUP BY EmpSalary ORDER BY EmpSalary DESC LIMIT $nth ,1;

cet

赞同来自:

如果你使用子查询太简单了!

SELECT MIN(EmpSalary) from (
SELECT EmpSalary from Employee ORDER BY EmpSalary DESC LIMIT 3
);
您可以在此处更改LIMIT约束后的第n个值。 在这里,Sub查询从雇员订单中选择EmpSalary DESC限制3;将返回员工的前3名工资。在结果中,我们将使用MIN命令选择最低工资以获得员工的第3个TOP工资。

zquam

赞同来自:

SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

ciste

赞同来自:

行号:

SELECT Salary,EmpName
FROM
  (
   SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
   FROM EMPLOYEE
   ) As A
WHERE A.RowNum IN (2,3)
子查询:
SELECT *
FROM Employee Emp1
WHERE (N-1) = (
               SELECT COUNT(DISTINCT(Emp2.Salary))
               FROM Employee Emp2
               WHERE Emp2.Salary > Emp1.Salary
               )
热门关键字:
SELECT TOP 1 salary
FROM (
      SELECT DISTINCT TOP n salary
      FROM employee
      ORDER BY salary DESC
      ) a
ORDER BY salary

fdolor

赞同来自:

这是任何SQL访谈中的热门问题之一。我将写下不同的查询以找出列的第n个最高值。 我通过运行以下脚本创建了一个名为“Emloyee”的表。

CREATE TABLE Employee(Eid] [float] NULL,[Ename] [nvarchar NULL,[Basic_Sal] [float] NULL)
现在,我将通过运行下面的insert语句在该表中插入8行。
insert into Employee values(1,'Neeraj',45000)
insert into Employee values(2,'Ankit',5000)
insert into Employee values(3,'Akshay',6000)
insert into Employee values(4,'Ramesh',7600)
insert into Employee values(5,'Vikas',4000)
insert into Employee values(7,'Neha',8500)
insert into Employee values(8,'Shivika',4500)
insert into Employee values(9,'Tarun',9500)
现在我们将使用不同的查询从上表中找出第三高的Basic_sal。 我在管理工作室运行以下查询,结果如下。
select * from Employee order by Basic_Sal desc
我们可以在上面的图像中看到第三高的基本工资是8500.我正在写3种不同的方式来做同样的事情。通过运行下面提到的所有三个查询,我们将获得相同的结果,即8500。 第一种方式: - 使用行号功能
select Ename,Basic_sal
from(
            select Ename,Basic_Sal,ROW_NUMBER() over (order by Basic_Sal desc) as rowid from Employee
      )A
where rowid=2

inemo

赞同来自:

declare @maxNthSal as nvarchar(20)
SELECT TOP 3 @maxNthSal=GRN_NAME FROM GRN_HDR   ORDER BY GRN_NAME DESC
print @maxNthSal

uid

赞同来自:

SELECT TOP 1 salary FROM (
   SELECT TOP 3 salary 
   FROM employees 
   Group By salary ORDER BY salary DESC ) AS emp 
ORDER BY salary ASC

laut

赞同来自:

SELECT MIN(COLUMN_NAME)
FROM   (
           SELECT DISTINCT TOP 3     COLUMN_NAME
           FROM   TABLE_NAME
           ORDER BY
                  COLUMN_NAME        DESC
       ) AS 'COLUMN_NAME'

aid

赞同来自:

只需更改内部查询值:E.g按ClassID desc从Student_Info顺序中选择Top(2)* 用于这两个问题:

Select Top (1)* from 
(
 Select Top (1)* from Student_Info order by ClassID desc 
) as wsdwe
order by ClassID 

znihil

赞同来自:

不使用子查询的工资表中的第三或第n最高工资

select salary from salary
   ORDER   BY salary DESC
   OFFSET  N-1 ROWS
   FETCH NEXT 1 ROWS ONLY
第三高的薪水代替N-1

hea

赞同来自:

另一种根据日期查找最高数据的方法

SELECT A.JID,A.EntryDate,RefundDate,Comments,Refund, ActionBy FROM (
(select JID, Max(EntryDate) AS EntryDate from refundrequested GROUP BY JID) A 
Inner JOIN (SELECT JID,ENTRYDATE,refundDate,Comments,refund,ActionBy from refundrequested) B 
ON A.JID=B.JID AND A.EntryDate = B.EntryDate) 

pquis

赞同来自:

要使用AdventureWorks2012查询nth highest bonus,例如n=10,请尝试以下代码

USE AdventureWorks2012; 
GO
SELECT * FROM Sales.SalesPerson;
GO
DECLARE @grade INT;
SET @grade = 10;
SELECT MIN(Bonus)
FROM (SELECT TOP (@grade) Bonus FROM (SELECT DISTINCT(Bonus) FROM Sales.SalesPerson) AS a ORDER BY Bonus DESC) AS g

equi

赞同来自:

找到第五高薪:

Declare @N INT = 5 
SELECT Salary FROM Employee 
ORDER BY Salary DESC OFFSET @N - 1 ROW

xsint

赞同来自:

使用ROW_NUMBER(如果需要单个)或DENSE_RANK(适用于所有相关行):

WITH CTE AS
(
    SELECT EmpID, EmpName, EmpSalary,
           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
    FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow

xporro

赞同来自:

尝试这个

SELECT TOP 1 salary FROM (
   SELECT TOP 3 salary 
   FROM employees 
   ORDER BY salary DESC) AS emp 
ORDER BY salary ASC
对于3你可以替换任何值...

baut

赞同来自:

  • 最高薪水
    select * 
    from (select lstName, salary, row_number() over( order by salary desc) as rn 
       from employee) tmp
    where rn = 2
    
    - (n-1)最高薪水
    select * 
    from employee e1
    where 1 = (select count(distinct salary)  
            from employee e2
            where e2.Salary > e1.Salary )
    

gquia

赞同来自:

用最大数字替换N.

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
说明 如果您之前没有看到类似的内容,则上面的查询会非常混乱 - 内部查询是所谓的相关子查询,因为内部查询(子查询)使用外部查询中的值(在本例中为Emp1表) )在它的WHERE子句中。 和Source

xporro

赞同来自:

select max(sal) 
from emp 
where sal > (
    select max(sal) 
    from emp 
    where sal > (select max(sal) from emp)
);

menim

赞同来自:

select *  from emp x where &no=(select count(*) from emp y where y.sal>=x.sal);
这将给出来自用户的输入,然后将告诉第n个最大数字。我已经在oracle中获取了emp表的示例并显示了第n个最大受薪员工信息 产量 输入no:5的值
 EMPNO ENAME      JOB       MGR        HIREDATE  SAL        COMM       DEPTNO
 ----- ---------- --------- ---------- --------- ---------- ---------- ----------
 7698  BLAKE      MANAGER   7839       01-MAY-81 3000                  30
 7788  SCOTT      ANALYST   7566       19-APR-87 3000                  20
 7902  FORD       ANALYST   7566       03-DEC-81 3000                  20
输入no:14的值
 EMPNO ENAME      JOB       MGR        HIREDATE  SAL        COMM       DEPTNO
 ----- ---------- --------- ---------- --------- ---------- ---------- ----------
 7369  SMITH      CLERK     7902       17-DEC-80 800                   20