mysql - 查询最后促销日期的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

您可以使用相关子查询过滤每个员工的最新职位，然后聚合:

select empid, title, min(startdate)
from emp e
where e.title = (
    select e1.title 
    from emp e1 
    where e1.empid = e.empid 
    order by startdate desc 
    limit 1
)
group by empid, title
order by empid desc

Demo on DB Fiddle :

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