MySQL分组查询

Question

我正在尝试根据日程表(日程表)计算一个人的当前位置。

基本前提是，您可以安排一个人在办公室工作一段时间(假设 start_date=2015-10-01，end_date=2015-12-31)。那是一个日程项目。它与位置有 1toM 关系 - 没问题，我已经对这部分进行了排序。

但是，虽然他们被安排在该办公室，但他们也可能被安排去异地/客户办公室。因此，将有另一个计划条目，例如，start_date=2015-12-03，end_date=2015-12=04。

这是表结构。

Person table
----------------------------------------------
|person_id |person_name  |person_email       |
----------------------------------------------
|1         |John         |john@example.org   |
|2         |Jane         |jane@example.org   |
----------------------------------------------

Schedule table
--------------------------------------------------------------
|schedule_id |person_id |location_id |start_date |end_date   |
--------------------------------------------------------------
|1           |1         |1           |2015-10-01 |2015-12-31 |
|2           |2         |2           |2015-10-15 |2016-01-15 |
|3           |1         |5           |2015-12-03 |2015-12-10 |
|4           |2         |7           |2015-12-04 |2015-12-12 |
--------------------------------------------------------------

当我查询单个记录时，我很容易计算出此人当前所在的位置。没那么复杂。

SELECT * FROM schedules
WHERE person_id = 1 AND start_date <= CURDATE() AND end_date >= CURDATE
ORDER BY end_date ASC, start_date DESC
LIMIT 0,1

但是，当我需要生成一个包含所有人员及其当前日程项目的列表时，我遇到了问题。我最初想在查询中使用 GROUP BY 语句，但它只会返回与查询匹配的最早的计划项目。

其中的问题是，有多个计划项目将匹配查询(这是域逻辑的一部分)。但是，我将始终选择最短的当前任期作为他们的当前位置。

我过去曾使用分组查询来根据最近的状态条目计算一个人的就业状态。但是，由于计划项内部和周围有一些稍微复杂的逻辑(它包含 future 的计划项)，我真的只是在自言自语地讨论最佳方法。

Answer 1

一种使用带有 substring_index 的子查询的方法。这将获取所有按结束日期和开始日期之间的时间长度排序的计划 ID，然后使用 SUBSTRING_INDEX 仅获取第一个。然后将此与时间表结合起来以获取其余详细信息。

SELECT *
FROM schedules
INNER JOIN 
(
    SELECT person_id, SUBSTRING_INDEX(GROUP_CONCAT(schedule_id ORDER BY DATEDIFF(end_date, start_date)), ',', 1) AS best_schedule_id
    FROM schedules
    WHERE person_id = 1 
    AND start_date <= CURDATE() AND end_date >= CURDATE
    GROUP BY person_id
) sub0
ON schedules.schedule_id = sub0.best_schedule_id
AND schedules.person_id = sub0.person_id

请注意，我还从子查询中返回了人员 ID。由于目前的查询不是绝对必要的，但将其放在适当的位置，这样如果您开始想要召回多个人，它几乎不需要改变。