用于查找上次升级日期的SQL语句

Question

我们将非常感谢您的帮助。我正在尝试从数据集中查找所有员工最近一次晋升的最后日期。数据集的示例如下所示。数据集-

mysql> select * from emp;

+----+-------+-----------+------------+
| id | empid | title     | startdate  |
+----+-------+-----------+------------+
|  1 |   111 | Associate | 2017-11-01 |
|  2 |   222 | ED        | 2017-11-01 |
|  3 |   111 | Associate | 2017-12-01 |
|  4 |   222 | MD        | 2017-12-01 |
|  5 |   111 | VP        | 2018-01-01 |
|  6 |   222 | MD        | 2018-01-01 |
|  7 |   111 | VP        | 2018-02-01 |
|  8 |   222 | MD        | 2018-02-01 |
+----+-------+-----------+------------+

我想要的输出-

+-------+-----------+----------------+
| empid | title     | min(startdate) |
+-------+-----------+----------------+
|   222 | MD        | 2017-12-01     |
|   111 | VP        | 2018-01-01     |
+-------+-----------+----------------+ 

我尝试过这个SQL -

mysql> select empid, title, min(startdate) 
       from ( select * from emp ) x 
       group by title, empid 
       order by empid desc;

它输出所有组，如下所示-

+-------+-----------+----------------+
| empid | title     | min(startdate) |
+-------+-----------+----------------+
|   222 | MD        | 2017-12-01     |
|   222 | ED        | 2017-11-01     |
|   111 | Associate | 2017-11-01     |
|   111 | VP        | 2018-01-01     |
+-------+-----------+----------------+

Answer 1

只接受empid和max(startdate)作为子查询，并将其连接回主表：

SELECT *
FROM emp e
  INNER JOIN 
  (SELECT empid, Max(startdate) recentpromo FROM emp GROUP BY empid) x
  ON x.empid = e.empid AND x.recentpromo = e.startdate

如果你使用的是mysql8，你可以使用ROW_NUMBER作为替代。如果一名员工在同一天被提升了两次，那么这两个记录都会出现，不过我认为这种情况不太可能发生

编辑，因此我们可以将其扩展到另一个级别：

SELECT
  em.empid,
  em.title,
  min(em.startdate) as

FROM
  emp em
  INNER JOIN
  (
    SELECT e.empid, e.title
    FROM emp e
      INNER JOIN 
      (SELECT empid, Max(startdate) recentpromo FROM emp GROUP BY empid) x
      ON x.empid = e.empid AND x.recentpromo = e.startdate
  ) frpt --find recent promo title
  ON 
    em.empid = frpt.empid AND
    em.title = frpt.title
GROUP BY
  em.empid, em.title