牛客网sql编程题

1.

• 题目描述
  查找最晚入职员工的所有信息
  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));
• 题目解答：

select *
from employees
order by hire_date desc
limit 0,1

select * from employees
where hire_date =
(select max(hire_date) from employees)

• 题目描述

查找入职员工时间排名倒数第三的员工所有信息
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));

• 题目解答：

select *
from
employees
where hire_date=(select distinct hire_date 
                 from employees 
                 order by hire_date desc 
                 limit 2,1);

select * from employees e1
where 2=(select count(*) 
         from employees e2 
         where e1.hire_date < e2.hire_date);