SQL将特定年份的值与平均值进行比较 [英] SQL comparing value to average value for a specific year

问题描述

期望的产出:我想选择1991年薪水高于1991年平均水平的雇员的所有头衔.

Desired output : I want to select all titles of employees who in 1991 had a higher salary than the average in 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.

The from_date in the salaries table has to be used.

推荐答案

首先，薪水表中包含时间范围.一个人本可以在1991年全部工作，只赚一份薪水.例如，从1989年到2000年以相同的薪水工作的人就是这种情况.但是一个人也可以在一年中的某个时间工作以一份薪水，而另一部分工作以另一份薪水(例如，7月加薪).一个人可能是1991年12月1日开始的.或者是1991年1月15日退出的.您如何计算平均值?

First of all there is time range in the salary table. A person can have worked all 1991 for one salary. That would be the case for instance for a person who worked from 1989 till 2000 for the same salary. But a person can also have worked part of the year for one salary and the other part for another salary (e.g. a salary rise in July). And a person may have started in December 1, 1991. Or quit on January 15, 1991. How do you want to calculate the average?

假设您只计算1991年的所有薪水记录. A人在一月工作了1000年，其余时间为3000年，B人全年工作了1000年，而C人仅在2000年6月工作，而在7月仅4000年工作.计算:(1000 + 3000 + 1000 + 3300 + 3700)/5 =2400.1991年工作的薪水高于2400的人是A和C.

Let's say you simply count all salary records in 1991. E.g. person A worked in January for 1000 and the rest of the year for 3000, person B worked all year for 1000 and person C only worked in June for 2000 and in July for 4000. The calculation: (1000 + 3000 + 1000 + 3300 + 3700) / 5 = 2400. The persons who worked in 1991 for a higher salary than 2400 are A and 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:6月的新薪水，7月的薪水上涨将是一个薪水较高的职位.)

You may want to restrict the titles to 1991, too. I don't know. Maybe you even want them to match the time spans where the salary was higher than the average. In that case you'd have to look for overlapping time ranges. (Example 1: Salary rise in June, new title in July would be two titles for the high salary. Example 2: New title in June, salary rise in July would be one title with the high salary.)

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

If you have MySQL 8.0 you can use a WITH clause to select the 1991 salaries to get the query more readable.

这篇关于SQL将特定年份的值与平均值进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！