时间戳

首页 > 解决方案 > SQL将值与特定年份的平均值进行比较

mysql - SQL将值与特定年份的平均值进行比较

问题描述

期望的输出：我想选择 1991 年工资高于 1991 年平均水平的所有员工的职称。

当前代码：

USE employees;
SELECT t.title
FROM employees emps INNER JOIN
 employees.salaries s
 ON s.emp_no = emps.emp_no INNER JOIN
 employees.titles t
 ON t.emp_no = s.emp_no
 WHERE s.from_date LIKE '1991%'  AND s.salary >

员工表：

CREATE TABLE employees (
emp_no      INT             NOT NULL,
birth_date  DATE            NOT NULL,
first_name  VARCHAR(14)     NOT NULL,
last_name   VARCHAR(16)     NOT NULL,
gender      ENUM ('M','F')  NOT NULL,    
hire_date   DATE            NOT NULL,
PRIMARY KEY (emp_no)

工资表：

CREATE TABLE salaries (
emp_no      INT             NOT NULL,
salary      INT             NOT NULL,
from_date   DATE            NOT NULL,
to_date     DATE            NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)

标题表：

CREATE TABLE titles (
emp_no      INT             NOT NULL,
title       VARCHAR(50)     NOT NULL,
from_date   DATE            NOT NULL,
to_date     DATE,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no,title, from_date)

必须使用薪金表中的 from_date。

标签： mysqlsql

解决方案

首先，工资表中有时间范围。一个人可以为一份薪水工作整个 1991 年。例如，对于从 1989 年到 2000 年以相同工资工作的人来说就是这种情况。但是一个人也可以在一年中的一部分时间为一份薪水而另一部分为另一份薪水（例如，七月份的工资上涨）。一个人可能从 1991 年 12 月 1 日开始。或者在 1991 年 1 月 15 日退出。你想如何计算平均值？

假设您简单地计算了 1991 年的所有工资记录。例如，A 人 1 月份工作 1000，其余时间 3000，B 人全年工作 1000 人，C 人仅在 6 月工作 2000 年，7 月工作 4000 人。计算：(1000 + 3000 + 1000 + 3300 + 3700) / 5 = 2400。1991年工资高于2400的人是A和C。

select *
from titles 
where emp_no in
(
  select emp_no
  from salaries
  where from_date <= date '1991-12-31' and to_date >= date '1991-01-01'
  and salary >
  (
    select avg(salary)
    from salaries
    where from_date <= date '1991-12-31' and to_date >= date '1991-01-01'
  )
);

您可能还想将标题限制为 1991 年。我不知道。也许您甚至希望他们匹配工资高于平均水平的时间跨度。在这种情况下，您必须寻找重叠的时间范围。（例1：6月加薪，7月新职称是2个高薪职称。例2：6月新职称，7月加薪是1个高薪职称。）

如果您有 MySQL 8.0，则可以使用WITH子句选择 1991 年的薪水，以使查询更具可读性。

推荐阅读