mysql - SQL查询以获取2个日期有效时间表之间的数据

Question

当 margareta 担任经理时，我需要获取 georgi facello 的薪水数据。

这是位置(标题待定)表:

___________________________________________________
| name      | title     | from_date   | to_date    |
----------------------------------------------------
| Margareta | Manager   | 1985-01-01  | 1991-10-01 |

这是员工工资表:

___________________________________________________
| name         | salary   | from_date   | to_date  |
----------------------------------------------------
Georgi Facello      60117   1986-06-26  1987-06-26
Georgi Facello      62102   1987-06-26  1988-06-25
Georgi Facello      66074   1988-06-25  1989-06-25
Georgi Facello      66596   1989-06-25  1990-06-25
Georgi Facello      66961   1990-06-25  1991-06-25
Georgi Facello      71046   1991-06-25  1992-06-24
Georgi Facello      74333   1992-06-24  1993-06-24
Georgi Facello      75286   1993-06-24  1994-06-24
Georgi Facello      75994   1994-06-24  1995-06-24
Georgi Facello      76884   1995-06-24  1996-06-23
Georgi Facello      80013   1996-06-23  1997-06-23
Georgi Facello      81025   1997-06-23  1998-06-23
Georgi Facello      81097   1998-06-23  1999-06-23
Georgi Facello      84917   1999-06-23  2000-06-22
Georgi Facello      85112   2000-06-22  2001-06-22
Georgi Facello      85097   2001-06-22  2002-06-22
Georgi Facello      88958   2002-06-22  9999-01-01

所以我的查询是

SELECT employees.name, salaries.*
FROM salaries
INNER JOIN employees ON employees.emp_no = salaries.emp_no
WHERE salaries.from_date >= (
    SELECT titles.from_date
    FROM titles 
    JOIN employees ON employees.emp_no = titles.emp_no
    WHERE titles.title = 'manager' AND employees.name = 'margareta markovitch'
) 
AND salaries.to_date <= (
    SELECT titles.to_date
    FROM titles 
    JOIN employees ON employees.emp_no = titles.emp_no
    WHERE titles.title = 'manager' AND employees.name = 'margareta markovitch'
) 

这是结果

 ___________________________________________________
| name         | salary   | from_date   | to_date  |
----------------------------------------------------
Georgi Facello      60117   1986-06-26  1987-06-26
Georgi Facello      62102   1987-06-26  1988-06-25
Georgi Facello      66074   1988-06-25  1989-06-25
Georgi Facello      66596   1989-06-25  1990-06-25
Georgi Facello      66961   1990-06-25  1991-06-25

问题是结果中没有包含一行:

Georgi Facello      71046   1991-06-25  1992-06-24

这仍然有效，因为 margareta 在 91 年 10 月之前仍然是经理

你对这个问题有什么建议吗？

Answer 1

你们很亲近。只需要包括所有重叠的时期:

 SELECT DISTINCT employees.name, salaries.*
    FROM salaries
    INNER JOIN employees ON employees.emp_no = salaries.emp_no
    WHERE salaries.from_date < (
        SELECT titles.to_date
        FROM titles 
        JOIN employees ON employees.emp_no = titles.emp_no
        WHERE titles.title = 'manager' AND employees.name = 'margareta markovitch'
    ) 
    AND salaries.to_date > (
        SELECT titles.from_date
        FROM titles 
        JOIN employees ON employees.emp_no = titles.emp_no
        WHERE titles.title = 'manager' AND employees.name = 'margareta markovitch'
    )