mysql - SQL:从历史表中获取最新条目

Question

我有3张 table

person (id, name)
area (id, number)
history (id, person_id, area_id, type, datetime)

在这个表中，我存储了哪个人在特定时间拥有哪个区域的信息。这就像一个推销员在一个地区旅行了一段时间，然后他又到了另一个地区。他还可以一次拥有多个区域。

历史记录类型 =“I”( checkin )或“O”( checkout )。示例:

id    person_id    area_id    type    datetime
1     2            5          'O'     '2011-12-01' 
2     2            5          'I'     '2011-12-31' 
A person started traveling in area 5 at 2011-12-01 and gave it back on 2011-12-31.

现在我想要一份所有人现在拥有的所有区域的列表。

person1.name, area1.number, area2.number, area6.name
person2.name, area5.number, area9.number
....

输出也可能是这样的(没关系):

person1.name, area1.number
person1.name, area2.number
person1.name, area6.number
person2.name, area5.number
....

我怎样才能做到这一点？

Answer 1

这个问题确实很棘手。您需要历史记录的列表，其中对于给定的用户和区域，有一个“O”记录，没有后续的“I”记录。仅使用历史表，这意味着:

SELECT ho.person_id, ho.area_id, ho.type, MAX(ho.datetime)
  FROM History AS ho
 WHERE ho.type = 'O'
   AND NOT EXISTS(SELECT *
                    FROM History AS hi
                   WHERE hi.person_id = ho.person_id
                     AND hi.area_id   = ho.area_id
                     AND hi.type = 'I'
                     AND hi.datetime > ho.datetime
                 )
 GROUP BY ho.person_id, ho.area_id, ho.type;

然后，由于您实际上只需要人名和地区号码(尽管我不确定为什么地区号码不能与其 ID 相同)，所以您需要稍微调整一下，加入额外的内容两个表:

SELECT p.name, a.number
  FROM History AS ho
  JOIN Person  AS p  ON ho.person_id = p.id
  JOIN Area    AS a  ON ho.area_id   = a.id
 WHERE ho.type = 'O'
   AND NOT EXISTS(SELECT *
                    FROM History AS hi
                   WHERE hi.person_id = ho.person_id
                     AND hi.area_id   = ho.area_id
                     AND hi.type = 'I'
                     AND hi.datetime > ho.datetime
                 );

NOT EXISTS 子句是相关子查询；这往往是低效的。您也许可以将其重新转换为具有适当连接和过滤条件的 LEFT OUTER JOIN:

SELECT p.name, a.number
  FROM History AS ho
  JOIN Person  AS p  ON ho.person_id = p.id
  JOIN Area    AS a  ON ho.area_id   = a.id
  LEFT OUTER JOIN History AS hi
    ON hi.person_id = ho.person_id
   AND hi.area_id   = ho.area_id
   AND hi.type = 'I'
   AND hi.datetime > ho.datetime
 WHERE ho.type = 'O'
   AND hi.person_id IS NULL;

所有 SQL 均未经验证。