江西建设信息港网站,国外点击链接推广平台,seo网站地图,php网站 怎么做授权1. 牛客SQL热题206#xff1a;获取每个部门中当前员工薪水最高的相关信息
1.1 题目#xff1a;
描述
有一个员工表dept_emp简况如下:
emp_nodept_nofrom_dateto_date10001d0011986-06-269999-01-0110002d0011996-08-039999-01-0110003d0021996-08-039999-01-01 有一个薪水…1. 牛客SQL热题206获取每个部门中当前员工薪水最高的相关信息
1.1 题目
描述
有一个员工表dept_emp简况如下:
emp_nodept_nofrom_dateto_date10001d0011986-06-269999-01-0110002d0011996-08-039999-01-0110003d0021996-08-039999-01-01 有一个薪水表salaries简况如下:
emp_nosalaryfrom_dateto_date10001889582002-06-229999-01-0110002725272001-08-029999-01-0110003925272001-08-029999-01-01 获取每个部门中当前员工薪水最高的相关信息给出dept_no, emp_no以及其对应的salary按照部门编号dept_no升序排列以上例子输出如下:
dept_noemp_nomaxSalaryd0011000188958d0021000392527
示例1
输入drop table if exists dept_emp ;
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 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_emp VALUES(10003,d002,1996-08-03,9999-01-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,92527,2001-08-02,9999-01-01);
复制输出d001|10001|88958
d002|10003|92527
1.2 思路
一个相关子查询即可搞定。
1.3 题解
with tep1 as (-- 两表连接select t1.emp_no, dept_no, salaryfrom dept_emp t1join salaries t2on t1.emp_no t2.emp_no
)select dept_no, emp_no, salary maxSalary
from tep1 t1
where salary (-- 相关子查询查找外部记录所在部门的最高薪水select max(salary)from tep1 t2where t1.dept_no t2.dept_no
)
order by dept_no
2. 牛客SQL热题205获取所有员工当前的manager
2.1 题目
描述
有一个员工表dept_emp简况如下:
emp_nodept_no from_date to_date10001d0011986-06-269999-01-0110002d0011996-08-039999-01-0110003d0021995-12-039999-01-01
第一行表示为员工编号为10001的部门是d001部门。 有一个部门经理表dept_manager简况如下:
dept_noemp_nofrom_date to_dated001100021996-08-039999-01-01d002100031990-08-059999-01-01
第一行表示为d001部门的经理是编号为10002的员工。 获取所有的员工和员工对应的经理如果员工本身是经理的话则不显示以上例子如下:
emp_nomanager1000110002 示例1
输入drop table if exists dept_emp ;
drop table if exists dept_manager ;
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));
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,1995-12-03,9999-01-01);
INSERT INTO dept_manager VALUES(d001,10002,1996-08-03,9999-01-01);
INSERT INTO dept_manager VALUES(d002,10003,1990-08-05,9999-01-01);
复制输出10001|10002
2.2 思路
秒。
2.3 题解
select t1.emp_no emp_no, t2.emp_no manager
from dept_emp t1
join dept_manager t2
on t1.dept_no t2.dept_no
and t1.emp_no t2.emp_no
3. 牛客SQL热题199查找所有员工的last_name和first_name以及对应部门编号的dept_no
3.1 题目
描述
有一个员工表employees简况如下:
emp_no birth_date first_name last_name genderhire_date 100011953-09-02Georgi Facello M1986-06-26100021964-06-02Bezalel Simmel F1985-11-2110003 1959-12-03Parto Bamford M1986-08-2810004 1954-05-01Christian Koblick M1986-12-01 有一个部门表dept_emp简况如下:
emp_nodept_no from_date to_date10001d0011986-06-269999-01-0110002d0021989-08-039999-01-01 请你查找所有已经分配部门的员工的last_name和first_name以及dept_no也包括暂时没有分配具体部门的员工以上例子如下:
last_name first_name dept_noFacello Georgi d001Simmel Bezalel d002BamfordPartoNULLKoblickChirstianNULL 示例1
输入drop table if exists dept_emp ;
drop table if exists employees ;
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 dept_emp VALUES(10001,d001,1986-06-26,9999-01-01);
INSERT INTO dept_emp VALUES(10002,d002,1996-08-03,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|d001
Simmel|Bezalel|d002
Bamford|Parto|None
Koblick|Chirstian|None
3.2 思路
秒。
3.3 题解
select last_name , first_name , dept_no
from employees t1
left join dept_emp t2
on t1.emp_no t2.emp_no
4. 牛客SQL热题198查找所有已经分配部门的员工的last_name和first_name以及dept_no
4.1 题目
描述
有一个员工表employees简况如下:
emp_no birth_date first_name last_name genderhire_date 100011953-09-02Georgi Facello M1986-06-26100021964-06-02Bezalel Simmel F1985-11-2110003 1959-12-03Parto Bamford M1986-08-2810004 1954-05-01Christian Koblick M1986-12-01 有一个部门表dept_emp简况如下:
emp_nodept_no from_date to_date10001d0011986-06-269999-01-0110002d0021989-08-039999-01-01 请你查找所有已经分配部门的员工的last_name和first_name以及dept_no未分配的部门的员工不显示以上例子如下:
last_name first_name dept_noFacello Georgi d001Simmel Bezalel d002 示例1
输入drop table if exists dept_emp ;
drop table if exists employees ;
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 dept_emp VALUES(10001,d001,1986-06-26,9999-01-01);
INSERT INTO dept_emp VALUES(10002,d002,1996-08-03,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|d001
Simmel|Bezalel|d002
4.2 思路
秒。
4.3 题解
select last_name , first_name , dept_no
from employees t1
join dept_emp t2
on t1.emp_no t2.emp_no
5. 牛客SQL热题197查找当前薪水详情以及部门编号dept_no
5.1 题目
描述
有一个全部员工的薪水表salaries简况如下:
emp_nosalaryfrom_dateto_date10001889582002-06-229999-01-0110002725272001-08-029999-01-0110003433112001-12-019999-01-01 有一个各个部门的领导表dept_manager简况如下:
dept_noemp_noto_dated001100019999-01-01d002100039999-01-01 请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no输出结果以salaries.emp_no升序排序并且请注意输出结果里面dept_no列是最后一列以上例子输出如下:
emp_nosalaryfrom_dateto_datedept_no10001889582002-06-229999-01-01d00110003433112001-12-019999-01-01d002
示例1
输入drop table if exists salaries ;
drop table if exists dept_manager ;
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 dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
INSERT INTO dept_manager VALUES(d001,10002,9999-01-01);
INSERT INTO dept_manager VALUES(d002,10006,9999-01-01);
INSERT INTO dept_manager VALUES(d003,10005,9999-01-01);
INSERT INTO dept_manager VALUES(d004,10004,9999-01-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);
INSERT INTO salaries VALUES(10005,94692,2001-09-09,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);
复制输出10002|72527|2001-08-02|9999-01-01|d001
10004|74057|2001-11-27|9999-01-01|d004
10005|94692|2001-09-09|9999-01-01|d003
10006|43311|2001-08-02|9999-01-01|d002
5.2 思路
秒。
5.3 题解
select t1.emp_no, salary, from_date, t1.to_date, dept_no
from salaries t1
join dept_manager t2
on t1.emp_no t2.emp_no
order by t1.emp_no
6. 力扣hard题615平均薪资部门与公司比较
6.1 题目
表Salary
-------------------
| 列名 | 类型 |
-------------------
| id | int |
| employee_id | int |
| amount | int |
| pay_date | date |
-------------------
在 SQL 中id 是该表的主键列。
该表的每一行表示一个员工一个月的薪资。
employee_id 是来自 Employee 表的外键reference 列。 表 Employee
---------------------
| 列名 | 类型 |
---------------------
| employee_id | int |
| department_id | int |
---------------------
在 SQL 中employee_id 是该表的主键列。
该表的每一行表示一个员工所属的部门。 找出各个部门员工的平均薪资与公司平均薪资之间的比较结果更高 / 更低 / 相同。
以 任意顺序 返回结果表。
结果格式如下所示。 示例 1
输入
Salary 表:
-------------------------------------
| id | employee_id | amount | pay_date |
-------------------------------------
| 1 | 1 | 9000 | 2017/03/31 |
| 2 | 2 | 6000 | 2017/03/31 |
| 3 | 3 | 10000 | 2017/03/31 |
| 4 | 1 | 7000 | 2017/02/28 |
| 5 | 2 | 6000 | 2017/02/28 |
| 6 | 3 | 8000 | 2017/02/28 |
-------------------------------------
Employee 表:
----------------------------
| employee_id | department_id |
----------------------------
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
----------------------------
输出
--------------------------------------
| pay_month | department_id | comparison |
--------------------------------------
| 2017-02 | 1 | same |
| 2017-03 | 1 | higher |
| 2017-02 | 2 | same |
| 2017-03 | 2 | lower |
--------------------------------------
解释
在三月公司的平均工资是 (9000600010000)/3 8333.33...
部门 1 的平均薪资是 9000因为该部门只有一个员工其员工号为 1。因为 9000 8333.33所以比较结果为 higher
部门 2 的平均薪资是6000 10000/ 2 8000该平均薪资是员工号 2 和 3 的薪资的平均值。因为 8000 8333.33比较结果为 lower。根据同样的公式对于二月份的平均薪资比较结果为 same因为部门 1 和 2 都与公司的平均薪资相同即为 7000。
6.2 思路
两步走
-- 先求出每个月公司的平均薪资
-- 然后以部门和月份分组比较分组内的平均薪资与该月份公司的平均薪资的大小
6.3 题解
-- 先求出每个月公司的平均薪资
with tep1 as (select substring(pay_date, 1, 7) pay_month , round(avg(amount), 4) avgsfrom Salarygroup by substring(pay_date, 1, 7)
), tep2 as (-- 多表连接select t2.employee_id , department_id, amount , substring(pay_date, 1, 7) pay_month from Salary t1 join Employee t2 on t1.employee_id t2.employee_id
)-- 然后以部门和月份分组比较分组内的平均薪资与该月份公司的平均薪资的大小
-- 我没有用round过不了最后一个案例但使用round函数就可以过了。
-- 我也不知道为什么。
select pay_month, department_id,
case when round(avg(amount), 4) (select avgs from tep1 t2 where t1.pay_month t2.pay_month)
then higher
when round(avg(amount), 4) (select avgs from tep1 t2 where t1.pay_month t2.pay_month)
then lower
else same
end comparison
from tep2 t1
group by pay_month, department_id
