MySQL groupwise查询

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语句，但这将只返回与查询匹配的最早计划项。

其中的问题是有多个调度项目将匹配查询(这是域逻辑的一部分)。但是，我总是选择最短的当前时间作为它们的当前位置。

我以前使用过groupwise查询，根据最近的状态条目计算一个人的就业状态。然而，由于日程安排项的内部和周围有一些稍微复杂的逻辑(它包含未来的日程安排项)，所以我真的只是在反复讨论最好的方法。

Answer 1

您希望选择起始日期在当前日期之前、结束日期在当前日期之后的所有记录。你可以多次得到一个人。您要从该人员中选择结束日期最早的事件。这意味着您必须按结束日期对这些记录进行排序，并在person组中对它们进行编号。试试这个：

select * from (
    select a.scheduled_id
    , a.person_id
    , a.location_id
    , a.start_date
    , a.end_date
    , row_number() over (partition by a.person_id order by a.end_date) as rn
    from schedules a
    where getdate() between a.start_date and a.end_date
    ) tab
where rn=1

我后来添加了这个，因为我意识到MySQL中没有row_number函数。这就是MySQL版本。有点复杂，但它应该可以工作：

select * from (
    select @row_num := if(@prev_value=a.person_id,@row_num+1,1) as rn
    , a.scheduled_id
    , a.person_id
    , a.location_id
    , a.start_date
    , a.end_date
    , @prev_value := a.person_id as asgmnt
    from schedules a,
        (select @row_num:=1) x,
        (select @prev_value:=0) y
    where a.start_date<=curdate() and a.end_date>=curdate()
    order by a.person_id, a.end_date
    ) tab
where rn=1

Answer 2

使用子查询和substring_index的方法。这将获取按结束日期和开始日期之间的时间长度排序的所有计划ids，然后使用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

请注意，我还从子查询中返回了person id。不是严格意义上的查询，因为查询目前是必要的，但如果您开始想要带回多个人，则只需要很少的更改。

Answer 3

我从您给我的东西中获得了一些动力，在对输出执行GROUP BY之前，我决定对结果集执行另一个子查询。

SELECT s.schedule_id, s.person_id, s.location_id FROM (
    SELECT * FROM schedules
    WHERE person_id = 1 AND start_date <= CURDATE() AND end_date >= CURDATE
    ORDER BY end_date ASC, start_date DESC
) AS s GROUP BY s.person_id

这似乎给了我想要的结果集，除非有人能想到这会失败的原因？