sql5

题目描述

查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

    CREATE TABLE `employees` (

    `emp_no` int(11) NOT NULL,

    `birth_date` date NOT NULL,

    `first_name` varchar(14) NOT NULL,

    `last_name` varchar(16) NOT NULL,

    `gender` char(1) NOT NULL,

    `hire_date` date NOT NULL,

    PRIMARY KEY (`emp_no`));

    CREATE TABLE `salaries` (

    `emp_no` int(11) NOT NULL,

    `salary` int(11) NOT NULL,

    `from_date` date NOT NULL,

    `to_date` date NOT NULL,

    PRIMARY KEY (`emp_no`,`from_date`));

解答一：

思路：首先查到最高薪水的记录，然后去掉此记录的信息

最后在剩下的记录中查到最高的记录即为薪水排名第二的员工的信息了。

最高薪水：

select max(salary) from salaries where to_date='9999-01-01'

除去最高薪水：

select * from salaries where salary!=(

             select max(salary) from salaries where to_date='9999-01-01'

        )

1

剩下的最高薪水：

select max(salary) from (

        select * from salaries where salary!=(

            select max(salary) from salaries where to_date='9999-01-01'

        )

    ) where to_date='9999-01-01'

最后的完整代码：

    select s.emp_no,s.salary,e.last_name,e.first_name from salaries s,employees e 

    where s.emp_no=e.emp_no 

    and s.to_date='9999-01-01' 

    and s.salary=(

    select max(salary) from (

    select * from salaries where salary!=(

    select max(salary) from salaries where to_date='9999-01-01'

    )

    ) where to_date='9999-01-01'

    );

解答二：

也可以采用MAX（）函数的嵌套

    select e.emp_no, Max(s.salary) as salary, e.last_name, e.first_name

    from employees e,salaries s

    where e.emp_no=s.emp_no 

    and s.to_date='9999-01-01'

    and s.salary!= (select Max(salary) from salaries where to_date='9999-01-01')

解答三：

下面的解答思路是，借鉴了mysql分组取每组前几条记录的方法，优点在于可以取任意多薪水的员工，

缺点是不容易理解

    select s.emp_no, s.salary, e.last_name, e.first_name from salaries s join employees e on s.emp_no=e.emp_no

    and s.to_date='9999-01-01'

    and s.emp_no=(select s1.emp_no from salaries s1 join salaries s2 on s1.salary<=s2.salary 

      and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'

      group by s1.emp_no having count(1) = 2);

参考资料来源：牛客网