【力扣 + 牛客 | SQL题 | 每日4题】牛客SQL热题210,213,212,219
1. 力扣SQL1076:项目员工2
1.1 题目:
表:Project
+-------------+---------+ | Column Name | Type | +-------------+---------+ | project_id | int | | employee_id | int | +-------------+---------+ (project_id, employee_id) 是该表的主键(具有唯一值的列的组合)。 employee_id 是该表的外键(reference 列)。 该表的每一行都表明 employee_id 的雇员正在处理 Project 表中 project_id 的项目。
表:Employee
+------------------+---------+ | Column Name | Type | +------------------+---------+ | employee_id | int | | name | varchar | | experience_years | int | +------------------+---------+ employee_id 是该表的主键(具有唯一值的列)。 该表的每一行都包含一名雇员的信息。
编写一个解决方案来报告所有拥有最多员工的 项目。
以 任意顺序 返回结果表。
返回结果格式如下所示。
示例 1:
输入: Project table: +-------------+-------------+ | project_id | employee_id | +-------------+-------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | +-------------+-------------+ Employee table: +-------------+--------+------------------+ | employee_id | name | experience_years | +-------------+--------+------------------+ | 1 | Khaled | 3 | | 2 | Ali | 2 | | 3 | John | 1 | | 4 | Doe | 2 | +-------------+--------+------------------+ 输出: +-------------+ | project_id | +-------------+ | 1 | +-------------+ 解释: 第一个项目有3名员工,第二个项目有2名员工。
1.2 思路:
分组过滤即可。
1.3 题解:
-- 转念一想我为啥要用题目给的第二张表
select project_id
from Project
group by project_id
having count(*) >= all(
select count(*) from Project group by project_id
)
2. 牛客SQL热题210:统计出当前各个title类型对应的员工当前薪水对应的平均工资
2.1 题目:
描述
有一个员工职称表titles简况如下:
emp_no | title | from_date | to_date |
10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
10003 | Senior Engineer | 2001-12-01 | 9999-01-01 |
10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
10006 | Senior Engineer | 2001-08-02 | 9999-01-01 |
10007 | Senior Staff | 1996-02-11 | 9999-01-01 |
有一个薪水表salaries简况如下:
emp_no | salary | from_date | to_date |
10001 | 88958 | 1986-06-26 | 9999-01-01 |
10003 | 43311 | 2001-12-01 | 9999-01-01 |
10004 | 74057 | 1995-12-01 | 9999-01-01 |
10006 | 43311 | 2001-08-02 | 9999-01-01 |
10007 | 88070 | 2002-02-07 | 9999-01-01 |
请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg,并且以avg升序排序,以上例子输出如下:
title | avg(s.salary) |
Senior Engineer | 62409.2500 |
Senior Staff | 88070.0000 |
示例1
输入:
drop table if exists `salaries` ;
drop table if exists titles;
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`));
CREATE TABLE titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');
复制输出:
Senior Engineer|62409.2500
Senior Staff|88070.0000
2.2 思路:
聚合函数即可。
2.3 题解:
-- 感觉这题考察的就是反引号吧,因为avg是关键字
select title, avg(salary) `avg(s.salary)`
from titles t1
join salaries t2
on t1.emp_no = t2.emp_no
group by title
3. 牛客SQL热题213:查找所有员工的last_name和first_name以及对应的dept_name
3.1 题目:
描述
有一个员工表employees简况如下:
emp_no | birth_date | first_name | last_name | gender | hire_date |
10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
有一个部门表departments表简况如下:
dept_no | dept_name |
d001 | Marketing |
d002 | Finance |
d003 | Human Resources |
有一个,部门员工关系表dept_emp简况如下:
emp_no | dept_no | from_date | to_date |
10001 | d001 | 1986-06-26 | 9999-01-01 |
10002 | d001 | 1996-08-03 | 9999-01-01 |
10003 | d002 | 1990-08-05 | 9999-01-01 |
请你查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工,以上例子输出如下:
last_name | first_name | dept_name |
Facello | Georgi | Marketing |
Simmel | Bezalel | Marketing |
Bamford | Parto | Finance |
Koblick | Chirstian | NULL |
示例1
输入:
drop table if exists `departments` ;
drop table if exists `dept_emp` ;
drop table if exists `employees` ;
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
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`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO departments VALUES('d003','Human Resources');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
复制输出:
Facello|Georgi|Marketing
Simmel|Bezalel|Marketing
Bamford|Parto|Finance
Koblick|Chirstian|None
3.2 思路:
两个left join即可。
3.3 题解:
select last_name, first_name, dept_name
from employees t1
left join dept_emp t2
on t1.emp_no = t2.emp_no
left join departments t3
on t2.dept_no = t3.dept_no
4. 牛客SQL热题212:获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
4.1 题目:
描述
有一个员工表employees简况如下:
emp_no | birth_date | first_name | last_name | gender | hire_date |
10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
有一个薪水表salaries简况如下:
emp_no | salary | from_date | to_date |
10001 | 88958 | 2002-06-26 | 9999-01-01 |
10002 | 72527 | 2001-08-02 | 9999-01-01 |
10003 | 43311 | 2001-12-01 | 9999-01-01 |
10004 | 74057 | 2001-11-27 | 9999-01-01 |
请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不能使用order by完成,以上例子输出为:
(温馨提示:sqlite通过的代码不一定能通过mysql,因为SQL语法规定,使用聚合函数时,select子句中一般只能存在以下三种元素:常数、聚合函数,group by 指定的列名。如果使用非group by的列名,sqlite的结果和mysql 可能不一样)
emp_no | salary | last_name | first_name |
10004 | 74057 | Koblick | Chirstian |
示例1
输入:
drop table if exists `employees` ;
drop table if exists `salaries` ;
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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
复制输出:
10004|74057|Koblick|Chirstian
4.2 思路:
嵌套子查询。题目不让使用窗口函数。
4.3 题解:
select t1.emp_no, salary, last_name, first_name
from employees t1
join salaries t2
on t1.emp_no = t2.emp_no
where salary = (
select max(salary)
from salaries
where salary <> (
select max(salary)
from salaries
)
)
5. 牛客SQL热题219:获取员工其当前的薪水比其manager当前薪水还高的相关信息
5.1 题目:
描述
有一个,部门关系表dept_emp简况如下:
emp_no | dept_no | from_date | to_date |
10001 | d001 | 1986-06-26 | 9999-01-01 |
10002 | d001 | 1996-08-03 | 9999-01-01 |
有一个部门经理表dept_manager简况如下:
dept_no | emp_no | from_date | to_date |
d001 | 10002 | 1996-08-03 | 9999-01-01 |
有一个薪水表salaries简况如下:
emp_no | salary | from_date | to_date |
10001 | 88958 | 2002-06-22 | 9999-01-01 |
10002 | 72527 | 1996-08-03 | 9999-01-01 |
获取员工其当前的薪水比其manager当前薪水还高的相关信息,
第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
以上例子输出如下:
emp_no | manager_no | emp_salary | manager_salary |
10001 | 10002 | 88958 | 72527 |
示例1
输入:
drop table if exists `dept_emp` ;
drop table if exists `dept_manager` ;
drop table if exists `salaries` ;
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
复制输出:
10001|10002|88958|72527
5.2 思路:
找到员工表和管理者的薪水,join比较即可。
5.3 题解:
with tep1 as (
-- 先找到部门的每个人的薪水
select t1.emp_no, dept_no , salary emp_salary
from dept_emp t1
join salaries t2
on t1.emp_no = t2.emp_no
), tep2 as (
-- 再找到所有管理者的薪水
select t1.emp_no manager_no, dept_no , salary manager_salary
from dept_manager t1
join salaries t2
on t1.emp_no = t2.emp_no
)
-- join,将员工的薪资和经理的薪资比较
-- 员工表里其实也包含了经理。
select emp_no, manager_no, emp_salary, manager_salary
from tep1 t1
join tep2 t2
on t1.dept_no = t2.dept_no
where t1.emp_salary > t2.manager_salary