代码语言：javascript复制
在线Coding题目
例如：部门表（id，名称...），员工表（id，部门id，姓名，薪资，入职时间...），
查出部门中薪资最高的员工；部门薪资总和；
部门中入职时间在2022年4月份-2023年4月份之间的员工
table design
department 部门表
id varchar(32),
name varchar(255),
employee
id varchar(32),
name varchar(255),
department_id varchar(21),
job
id varchar(32),
name varchar(255),
job_salary
id varchar(32),
salary_monthly decimal(6,2),
job_id varchar(32),
employee_id varchar(32),
position
id varchar(32),
name varchar(255),
position_condition
id varchar(32),
empolyee_register_time datetime,
position_id varchar(32),
综合字段生成员工表
employee_position
id varchar(32),
department_id varchar(32), //部门id
name varchar(255), // 员工姓名
salary_monthly decimal(6,2), //员工工资
employee_register_time datetime, //员工入职时间
题目的查询sql
SELECT f.employee_name,
f.total_salary_depart
FROM
(
SELECT *
FROM
(
SELECT employee_name,
total_salary_depart,
max_salry_monthly_depart
FROM
(
SELECT *
FROM (
SELECT max(salary_monthly) AS max_salry_monthly_depart,
sum(salary_monthly) AS total_salary_depart,
substr(b.employee_reister_time, 0, 4) AS emp_resiter_date_year,
substr(b.employee_register_time, 4, 2) AS emp_resiter_date_month,
a.name as employee_name
FROM department a
LEFT JOIN employee_position b
ON a.id=b.department_id
GROUP BY department_id
) c
WHERE c.emp_register_date_year='2023'
) d
WHERE d.emp_register_date_month='01'
OR d.emp_register_date_month='02'
OR d.emp_register_date_month='03'
OR d.emp_register_date_month='04'
) e
ORDER BY max_salry_monthly_depart DESC
LIMIT 1
) f
union
SELECT f.employee_name,
f.total_salary_depart
FROM
(
SELECT *
FROM
(
SELECT employee_name,
total_salary_depart,
max_salry_monthly_depart
FROM
(
SELECT *
FROM (
SELECT max(salary_monthly) AS max_salry_monthly_depart,
sum(salary_monthly) AS total_salary_depart,
substr(b.employee_reister_time, 0, 4) AS emp_resiter_date_year,
substr(b.employee_register_time, 4, 2) AS emp_resiter_date_month,
a.name as employee_name
FROM department a
LEFT JOIN employee_position b
ON a.id=b.department_id
GROUP BY department_id
) c
WHERE c.emp_register_date_year='2022'
) d
WHERE d.emp_register_date_month='04'
OR d.emp_register_date_month='05'
OR d.emp_register_date_month='06'
OR d.emp_register_date_month='08'
OR d.emp_register_date_month='09'
OR d.emp_register_date_month='10'
OR d.emp_register_date_month='11'
OR d.emp_register_date_month='12'
) e
ORDER BY max_salry_monthly_depart DESC
LIMIT 1
) f
