18.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary2.md · __z2xy__/数据库SQL实战 - Gitee

forked from 柯基与佩奇/数据库SQL实战

Create your Gitee Account

Explore and code with more than 12 million developers，Free private repositories ！：）

This repository doesn't specify license. Please pay attention to the specific project description and its upstream code dependency when using it.

Clone or Download

18.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary2.md 1.29 KB

# 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by

## 题目描述

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

``` mysql
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
select s.emp_no,salary,last_name,first_name from salaries s left join employees e on e.emp_no = s.emp_no where s.salary = (select max(salary) from salaries where to_date = '9999-01-01' and salary != (select max(salary) from salaries))
```

## 题解

1、先获得去除了最大值得数据集t1

2、再获得t1中的最大值

Copy Edit Raw Blame History

authored 2020-09-25 10:18 . 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by

<!--
 * @Author: your name
 * @Date: 2020-09-21 17:24:24
 * @LastEditTime: 2020-09-25 10:18:46
 * @LastEditors: your name
 * @Description: In User Settings Edit
 * @FilePath: \database-sql-combat\18.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary2.md
-->
# 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by

## 题目描述

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

``` mysql
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
select s.emp_no,salary,last_name,first_name from salaries s left join employees e on e.emp_no = s.emp_no where s.salary = (select max(salary) from salaries where to_date = '9999-01-01' and salary != (select max(salary) from salaries))
```

## 题解

1、先获得去除了最大值得数据集t1

2、再获得t1中的最大值

马建仓 AI 助手

尝试更多

代码解读

代码找茬

代码优化

SQL

1

https://gitee.com/__z2xy__/database-sql-combat.git

git@gitee.com:__z2xy__/database-sql-combat.git

__z2xy__

database-sql-combat

数据库SQL实战

master