MySQL分组和排序

Question

mysql> describe jobs;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| job_id       | int(11) | NO   | PRI | NULL    | auto_increment |
| candidate_id | int(11) | NO   | MUL | NULL    |                |
| company_id   | int(11) | NO   | MUL | NULL    |                |
| start_date   | date    | NO   | MUL | NULL    |                |
| end_date     | date    | NO   | MUL | NULL    |                |
+--------------+---------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

每个候选人可以有多个工作。我想为每个候选人找到最新的工作(基于开始日期，因为结束日期可以是0000-00-00)，并检查(在PHP中)如果end_Date不是0000-00-00。

(如果您的最后一个结束日期不是0000-00-00，那么您目前处于失业状态，这正是我要找的；我不知道如何在我的查询中做到这一点，所以我将在PHP中完成)。

查询SELECT candidate_id, end_Date FROM jobs ORDER BY candidate_id , start_date DESC让我走了一半路。

+--------------+------------+
| candidate_id | end_Date   |
+--------------+------------+
|            1 | 2019-08-31 |
|            1 | 2019-01-31 |
|            1 | 2019-05-31 |
|            2 | 0000-00-00 |
|            2 | 2018-02-28 |
|            2 | 2017-05-31 |
|            2 | 2016-09-30 |
|            3 | 0000-00-00 |
|            3 | 2019-05-31 |
|            4 | 2019-04-30 |
|            4 | 2019-09-30 |

(如何)我只能获取每个候选人Id的第一个条目(具有最新start_date的行)？我只能得到结束日期不是0000-00-00的那些吗？

(糟糕，看起来我的end_date排序不起作用了)

Answer 1

您可以在max start_date group by候选人上使用连接

select  * 
from jobs j
inner join  (

  select candidate_id ,  max(start_date) max_start_date 
  from jobs
  group by   candidate_id

  ) t on t.candidate_id = j.candidate_id 
    and t.max_start_date = j.start_date 

Answer 2

您可以使用相关子查询进行筛选：

select j.*
from jobs j
where j.start_date = (
    select max(start_date)
    from jobs j1
    where j1.candidate_id = j.candidate_id and j1.end_date <> '0000-00-00'
)

对于当前候选对象，子查询返回其end_date不是null的最大start_date。

另一种解决每个组前1名问题的典型方法是使用anti-left join：

select j.*
from jobs j
left join jobs j1 
    on  j1.candidate_id = j.candidate_id
    and j1.start_date > j.start_date
    and j1.end_date is not null
where 
    j.end_date is not null
    and j1.job_id is null

此短语如下:给我具有非null end_date的记录，该记录不存在具有相同candidate_id、更大start_date和非null end_date的其他记录。

Answer 3

无子查询的选项：

SELECT
    j.*
FROM
    jobs AS j
    LEFT JOIN jobs AS j2 ON (
            j2.candidate_id = j.candidate_id
        AND j2.start_date   > j.start.date
    )
WHERE
    j2.candidate_id IS NULL

您希望使用复合索引(candidate_id、start_date)来优化查询。