如何在表中获得第二高的薪水员工

met 发布于 2018-12-02 optimization 最后更新 2018-12-02 15:01 63 浏览

这是我今天下午得到的一个问题: 在SQL Server中,有一张表包含ID,名称和雇员薪金,并获得第二高薪雇员的名称 这是我的答案,我只是写在纸上,并不确定它是完全有效的,但它似乎工作:

SELECT Name FROM Employees WHERE Salary = 
( SELECT DISTINCT TOP (1) Salary FROM Employees WHERE Salary NOT IN
 (SELECT DISTINCT TOP (1) Salary FROM Employees ORDER BY Salary DESCENDING)
ORDER BY Salary DESCENDING)
我认为这很丑陋,但这是我唯一想到的解决方案。 你能建议我一个更好的查询吗? 非常感谢你。
已邀请:

gest

赞同来自:

试试这个MSSQL:

SELECT
    TOP 1 salary
FROM
    (
        SELECT
            TOP 2 salary
        FROM
            Employees 
    ) sal
ORDER BY
    salary DESC;
但是你应该尝试这种适用于各种数据库的通用SQL查询。
SELECT
    MAX(salary)
FROM
    Employee
WHERE
    Salary NOT IN (
        SELECT
            Max(Salary)
        FROM
            Employee
    );
要么
SELECT
    MAX(Salary)
FROM
    Employee
WHERE
    Salary < (
        SELECT
            Max(Salary)
        FROM
            Employee
    );

id_aut

赞同来自:

尝试这个:

 WITH CTE AS
 (
 SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC)RN,* FROM Users
 )
 SELECT * FROM CTE WHERE RN=2

ieos

赞同来自:

使用此SQL,第二个最高薪水将使用员工姓名

Select top 1 start at 2 salary from employee group by salary order by salary desc;

yautem

赞同来自:

简单的方法没有使用特定于Oracle,MySQL等的任何特殊功能。 假设EMPLOYEE表具有如下数据。工资可以重复。

enter image description here 通过人工分析,我们可以按如下方式决定等级: -
enter image description here 查询可以实现相同的结果
select  *
from  (
select tout.sal, id, (select count(*) +1 from (select distinct(sal) distsal from     
EMPLOYEE ) where  distsal >tout.sal)  as rank  from EMPLOYEE tout
) result
order by rank
enter image description here 首先,我们找出不同的薪水。 然后我们发现不同薪水的数量大于每一行。 这只不过是那个id的等级。 对于最高薪水,此计数将为零。所以'+1'从1开始排名。 现在我们可以通过在上面的查询中添加where子句来获取第N个等级的ID。
select  *
from  (
select tout.sal, id, (select count(*) +1 from (select distinct(sal) distsal from     
EMPLOYEE ) where  distsal >tout.sal)  as rank  from EMPLOYEE tout
) result
where rank = N;

esaepe

赞同来自:

尝试此查询2ndHighest薪水:

SELECT MIN(salary) AS '2ndHighest'
  FROM tbl_Emp
  WHERE salary IN (SELECT TOP 2 salary FROM tbl_Emp ORDER BY 1 DESC)

lmagni

赞同来自:

SELECT *,DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk
INTO #tmp1
FROM Employees
SELECT * FROM #tmp1 WHERE Rnk = 2
DROP TABLE #tmp1
SELECT Name,SALARY
FROM  Employees
WHERE  Salary = (SELECT MIN(Salary)
             FROM   (SELECT DISTINCT TOP (2) Salary
                     FROM   Employees
                     ORDER  BY Salary DESC) T);

aeius

赞同来自:

declare
cntr number :=0;
cursor c1 is
select salary from employees order by salary desc;
z c1%rowtype;
begin
open c1;
fetch c1 into z;
while (c1%found) and (cntr <= 1) loop
cntr := cntr + 1;
fetch c1 into z;
dbms_output.put_line(z.salary);
end loop;
end;

cut

赞同来自:

试试这个

    select * from
   (
    select name,salary,ROW_NUMBER() over( order by Salary desc) as
    rownum from    employee
   ) as t where t.rownum=2
http://askme.indianyouth.info/details/write-a-sql-query-to-find-the-10th-highest-employee-salary-from-an-employee-table-explain-your-answer-111

ysit

赞同来自:

另一种直观的方式是: - 假设我们想找到第N个最高工资 1)按工资的降序对员工进行排序 2)使用rownum获取前N个记录。所以在这一步中,Nth记录是第N个最高薪水 3)现在按升序对此临时结果进行排序。因此,Nth最高薪水现在是第一个记录 4)从这个临时结果中获取第一条记录。 这将是第N个最高薪水。

select * from 
 (select * from 
   (select * from  
       (select * from emp order by sal desc)  
   where rownum<=:N )  
 order by sal )
where rownum=1;
如果有重复的工资,那么在最里面的查询中可以使用distinct。
select * from 
 (select * from 
   (select * from  
       (select distinct(sal) from emp order by 1 desc)  
   where rownum<=:N )  
 order by sal )
where rownum=1;

nipsa

赞同来自:

我认为这可能是最简单的。

SELECT Name FROM Employees group BY Salary DESCENDING limit 2;

xatque

赞同来自:

SELECT * 
FROM TABLE1 AS A 
WHERE NTH HIGHEST NO.(SELECT COUNT(ATTRIBUTE) FROM TABLE1 AS B) WHERE B.ATTRIBUTE=A.ATTRIBUTE;

zex

赞同来自:

这是简单的查询..如果你想要第二个最小值,那么只需将max更改为min并将less(<)符号更改为grater而不是(>)。

    select max(column_name) from table_name where column_name<(select max(column_name) from table_name)

uid

赞同来自:

尝试这个:

SELECT min(salary) 
FROM employee 
WHERE salary IN (SELECT top 2 salary FROM employee ORDER BY salary DESC)

in_ut

赞同来自:

select max(sal) , Department no. from employee where sal<max(sal)

gut

赞同来自:

 - Method 1
select max(salary) from Employees
        where salary< (select max(salary) from Employees)
- Method 2
select MAX(salary) from Employees 
    where salary not in(select MAX(salary) from Employees)
- Method 3
select MAX(salary) from Employees 
    where salary!= (select MAX(salary) from Employees )

prem

赞同来自:

SELECT
    salary
FROM
    Employee
ORDER BY
    salary DESC
LIMIT 1,
 1;

baut

赞同来自:

这是一个简单的方法:

select name
from employee
where salary=(select max(salary)
              from(select salary from employee
                   minus
                   select max(salary) from employee));

zomnis

赞同来自:

第n个最高薪水。这很简单

select t.name,t.sal
from (select name,sal,dense_rank() over (order by sal desc) as rank from emp) t
where t.rank=6; //suppose i find 6th highest salary

wsed

赞同来自:

SELECT name
FROM employee
WHERE salary =
(SELECT MIN(salary) 
  FROM (SELECT TOP (2) salary
  FROM employee
  ORDER BY salary DESC) )

bnemo

赞同来自:

创建临时表

Create Table #Employee (Id int identity(1,1), Name varchar(500), Salary int)
插入数据
Insert Into #Employee
    Select 'Abul', 5000
Union ALL 
    Select 'Babul', 6000
Union ALL 
    Select 'Kabul', 7000
Union ALL 
    Select 'Ibul', 8000
Union ALL 
    Select 'Dabul', 9000
查询将是
select top 1 * from #Employee a
Where a.id <> (Select top 1 b.id from #Employee b ORDER BY b.Salary desc)
order by a.Salary desc
放桌子
drop table #Empoyee

wnatus

赞同来自:

针对该问题的四种解决方案的汇编:

第一个解决方案 - 使用子查询 来自Employee的SELECT MAX(薪水)----此查询将为您提供最高薪水 现在使用上面的查询作为子查询来获得下一个最高薪水,如下所示: SELECT MAX(salary) FROM employee WHERE salary <> (SELECT MAX(salary) from Employee) - 此查询将为您提供第二高薪 现在,如果你想得到员工的名字获得第二高薪,那么使用上面的查询作为子查询得到它,如下
SELECT name from employee WHERE salary = 
(SELECT MAX(salary) FROM employee WHERE salary <> (SELECT MAX(salary) from Employee)
- 这个查询会给你第二高的薪水)

第二种解决方案 - 使用派生表 SELECT TOP 2 DISTINCT(salary) FROM employee ORDER BY salary DESC - 这将给你两个最高薪水。你在这里做的是按降序排列工资,然后选择前2名工资。 现在按工资按升序排序上面的结果集,得到TOP 1
SELECT TOP 1 salary FROM
(SELECT TOP 2 DISTINCT(salary) FROM employee ORDER BY salary DESC) AS tab 
ORDER BY salary

第三解决方案 - 使用相关子查询
SELECT name, salary FROM Employee e WHERE 2=(SELECT COUNT(DISTINCT salary) FROM Employee p WHERE e.salary<=p.salary)

第四种解决方案 - 使用窗口功能
;WITH T AS
(
SELECT *, DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk
FROM Employees
)
SELECT Name
FROM T
WHERE Rnk=2;

yeum

赞同来自:

SELECT `salary` AS emp_sal, `name` , `id`
FROM `employee`
GROUP BY `salary` ORDER BY `salary` DESC
LIMIT 1 , 1 

killo

赞同来自:

试试这个:这将给出动态结果,而不管没有行

SELECT * FROM emp WHERE salary = (SELECT max(e1.salary) 
FROM emp e1 WHERE e1.salary < (SELECT Max(e2.salary) FROM emp e2))**

somnis

赞同来自:

试试这个以获得相应的第n个最高薪水。

SELECT
    *
FROM
    emp e1
WHERE
    2 = (
        SELECT
            COUNT(salary)
        FROM
            emp e2
        WHERE
            e2.salary >= e1.salary
    )

xatque

赞同来自:

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

nomnis

赞同来自:

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)

qnon

赞同来自:

我们有一张桌子

name       salary
 A           10
 B           30
 C           20
 D           40
所以我们要做的是先按降序排列40 30 20 10 => 那么我们将只取前两个数= = 40 30 然后我们按升序排列=> 30 40 然后我们将取第一个数= = 30 所以在mysql ::
SELECT * FROM (SELECT  * FROM employee   order by salary DESC LIMIT 2) order by salary ASC LIMIT 1;
在oracle ::
SELECT * FROM (SELECT  * FROM employee  where rownum<=2 order by salary DESC )  where rownum<=1  order by salary ASC ;

gnihil

赞同来自:

select 
    max(salary) 
from 
    emp_demo_table 
where 
    salary < (select max(salary) from emp_demo_table)
希望这可以用最简单的术语解决查询。 谢谢

oest

赞同来自:

试试这个简单的方法

select name,salary from employee where salary =
(select max(salary) from employee where salary < (select max(salary) from employee ))

kquam

赞同来自:

找到第二高的薪水.... CODE_0的PLACEHOLDER 要么

SELECT max(salary) FROM tblEmp WHERE salary NOT IN (SELECT max(salary) FROM tblEmp)
其中“salary”是列名,tblEmp是表名... 两者都在100%工作......

cet

赞同来自:

我想在这里发布可能最简单的解决方案。它在mysql中工作。 请检查你的结尾:

SELECT name
FROM `emp`
WHERE salary = (
SELECT salary
FROM emp e
ORDER BY salary DESC
LIMIT 1
OFFSET 1 

nomnis

赞同来自:

select max(age) from yd where age<(select max(age) from HK) ; /// True two table Highest
SELECT * FROM HK E1 WHERE 1 =(SELECT COUNT(DISTINCT age) FROM HK E2 WHERE E1.age < E2.age); ///Second Hightest age RT single table
select age from hk e1 where (3-1) = (select count(distinct (e2.age)) from yd e2 where e2.age>e1.age);//// same True Second Hight age RT two table
select max(age) from YD where age not in (select max(age) from YD);  //second hight age in single table 

laut

赞同来自:

我们也可以使用

select e2.max(sal), e2.name
from emp e2
where (e2.sal <(Select max (Salary) from empo el))
group by e2.name
请让我知道这种方法有什么问题

miste

赞同来自:

如果要显示获得第二高工资的员工的姓名,请使用以下命令:

SELECT employee_name 
FROM employee
WHERE salary = (SELECT max(salary) 
                FROM employee
                WHERE salary < (SELECT max(salary) 
                                FROM employee);

wearum

赞同来自:

要获得可以使用的第二高工资额的员工姓名。

;WITH T AS
(
SELECT *,
       DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk
FROM Employees
)
SELECT Name
FROM T
WHERE Rnk=2;
如果Salary被编入索引,则以下可能更有效,尤其是如果有很多员工。
SELECT Name
FROM   Employees
WHERE  Salary = (SELECT MIN(Salary)
                 FROM   (SELECT DISTINCT TOP (2) Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) T);
测试脚本
CREATE TABLE Employees
  (
     Name   VARCHAR(50),
     Salary FLOAT
  )
INSERT INTO Employees
SELECT TOP 1000000 s1.name,
                   abs(checksum(newid()))
FROM   sysobjects s1,
       sysobjects s2
CREATE NONCLUSTERED INDEX ix
  ON Employees(Salary)
SELECT Name
FROM   Employees
WHERE  Salary = (SELECT MIN(Salary)
                 FROM   (SELECT DISTINCT TOP (2) Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) T);
WITH T
     AS (SELECT *,
                DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rnk
         FROM   Employees)
SELECT Name
FROM   T
WHERE  Rnk = 2;
SELECT Name
FROM   Employees
WHERE  Salary = (SELECT DISTINCT TOP (1) Salary
                 FROM   Employees
                 WHERE  Salary NOT IN (SELECT DISTINCT TOP (1) Salary
                                       FROM   Employees
                                       ORDER  BY Salary DESC)
                 ORDER  BY Salary DESC)
SELECT Name
FROM   Employees
WHERE  Salary = (SELECT TOP 1 Salary
                 FROM   (SELECT TOP 2 Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) sel
                 ORDER  BY Salary ASC)  

daut

赞同来自:

SELECT MIN(a.sal) 
FROM dbo.demo a 
WHERE a.sal IN (SELECT DISTINCT TOP 2 a.sal 
                FROM dbo.demo a 
                ORDER BY a.sal DESC) 

miure

赞同来自:

CTE怎么样?

;WITH Salaries AS
(
    SELECT Name, Salary,
       DENSE_RANK() OVER(ORDER BY Salary DESC) AS 'SalaryRank'
    FROM 
        dbo.Employees
)
SELECT Name, Salary
FROM Salaries  
WHERE SalaryRank = 2
DENSE_RANK()将为您提供薪水第二高的所有员工 - 无论有多少员工拥有(相同)最高薪水。

fdolor

赞同来自:

下面的查询可用于查找第n个最大值,只需从第n个数字中替换2

select * from emp e1 where 2 =(select count(distinct(salary)) from emp e2
   where e2.emp >= e1.emp)

est_ut

赞同来自:

此查询显示具有第二高薪的员工的所有详细信息

SELECT
    *
FROM
    Employees
WHERE
    salary IN (
        SELECT
            max(salary)
        FROM
            Employees
        WHERE
            salary NOT IN (
                SELECT
                    max(salary)
                FROM
                    Employees
            )
    );

zearum

赞同来自:

SELECT lastname, firstname
FROM employees
WHERE salary IN(
    SELECT MAX(salary) 
    FROM employees 
    WHERE salary < (SELECT MAX(salary) FROM employees));
所以这是上面提到的代码所做的: 它返回姓氏,后跟员工的名字 薪水小于所有员工的最高薪水,但也是没有最高薪水的其他员工的最高薪水。 换句话说:它返回具有第二个最高薪水的员工的姓名。

xipsam

赞同来自:

这可能对你有帮助

SELECT MIN(SALARY) FROM EMP WHERE SALARY = (SELECT DISTINCT TOP 2 SALARY FROM EMP ORDERBY SALARY DESC)
我们可以通过将'n'(n > 0)替换为'2'来找到任何nth最高薪水

fdolor

赞同来自:

select salary from table order by salary desc limit 1,1
注意:这仅适用于MYSQL

nnam

赞同来自:

我想您会想要使用DENSE_RANK,因为您不知道有多少员工拥有相同的薪水,而您确实说过您想要员工的名字。

CREATE TABLE #Test
(
    Id INT,
    Name NVARCHAR(12),
    Salary MONEY
)
SELECT x.Name, x.Salary
FROM
        (
        SELECT  Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as Rnk
        FROM    #Test
        ) x
WHERE x.Rnk = 2
ROW_NUMBER会为你提供唯一的编号,即使工资有关,如果你有多人争夺最高工资,普通的RANK就不会给你一个'2'作为排名。我已经纠正了这一点,因为DENSE_RANK做得最好。

kaut

赞同来自:

尝试这个:

SELECT *
FROM emptable 
WHERE empid IN (
    SELECT sal,row_number () ( OVER partition by sal order by sal desc) RN
    FROM emptable
    WHERE RN=2)

mqui

赞同来自:

select * from Employee where Salary In (select MAX(Salary) from Employee where Salary NOT IN (Select MAX(Salary) from employee));
试试这样..

malias

赞同来自:

select * from emp where salary = (  
    select salary from   
       (select ROW_NUMBER() over (order by salary) as 'rownum', *
        from emp) t -- Order employees according to salary  
    where rownum = 2 -- Get the second highest salary
)

zamet

赞同来自:

在这里,我使用了两个查询,以便在面试期间询问以下场景   第一种情况:
  找到表中所有第二高的薪水(第二高薪超过   一名员工)
  “选择来自emp的薪水    在(从工资中选择MAX(工资),其中工资不是IN(从中选择MAX(工资))    EMP));“点击    第二种情况:
  在表格中找到第二高的薪水   select from(temp.salary)from(select
from emp order by salary desc limit 2)   温度;

lquia

赞同来自:

select MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee )

et_et

赞同来自:

尝试这个

select * from (
   select ROW_NUMBER() over (order by [salary] desc) as sno,emp_name,   
   [salary] from [dbo].[Emp]
) t 
where t.sno =10
with t as
select top (1) * from       
  (select top (2) emp_name,salary from   [Emp]  e
   order by  salary desc) t
order  by salary asc

要回复问题请先登录注册