在线Coding题目

在线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