叨叨两句

  1. ~

SQL习题011

1

获取当前(to_date='9999-01-01')薪水第二多的员工的emp_no以及其对应的薪水salary
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 emp_no,salary
from salaries
where to_date = '9999-01-01'
order by salary desc
limit 1,1;

2

题目描述
查找当前薪水(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));

【注意】看看讨论链接,里面有个MySQL的坑
https://www.nowcoder.com/profile/1989208/codeBookDetail?submissionId=20473665

我不知道WebSQL上求max(salaries)会把所有的最高工资显示出来,还是只显示一个,
但是在MySQL中,只显示一个,这样如果第二高的工资人数有多个的话,上述方法会漏掉其他人,
select e.emp_no, salary, last_name, first_name
from employees as e inner join salaries as s
where e.emp_no = s.emp_no and s.to_date='9999-01-01'
and salary = 
(select max(salary) from salaries where salary < (select max(salary) from salaries)) 
这个是我的方案。

Wall_Breaker
2.1k 声望1.2k 粉丝

生死之间,就是我的跃迁之路,全程记录,欢迎见证