mysql - 高级 MySQL 查询

Question

  Employees
  EmpID : int(10)
  Firstname: varchar(100)
  Lastname: varchar(100)
  HireDate: timestamp
  TerminationDate: timestamp

  AnnualReviews
  EmpID: int(10)
  ReviewDate: timestamp

计算公司没有雇用或解雇任何人的最长时间(以天为单位)的查询是什么？

到目前为止，这是我的查询:

SELECT max(abs(datediff((select max(terminationdate) 
                         from employees 
                         where terminationdate < t.terminationdate),
               terminationdate))),
max(abs(datediff((select max(hiredate) 
                  from employees 
                  where hiredate <  t.hiredate), 
         hiredate)))
FROM employees AS t

我不知道如何比较同一字段中每一行的日期..

Answer 1

尽管如此，这仍然是一项艰巨的任务......

 SELECT x.date, MIN(y.date) y_date,DATEDIFF(MIN(y.date),x.date) days
FROM
(
SELECT hiredate date FROM employees
UNION 
SELECT terminationdate FROM employees
) x
JOIN
(
SELECT hiredate date FROM employees
UNION 
SELECT terminationdate FROM employees
UNION
SELECT CURDATE())
y
ON y.date > x.date
GROUP BY x.date
ORDER BY days DESC LIMIT 1;