这个问题确实很棘手.您需要历史记录中的条目列表，其中对于给定的用户和区域，存在"O"记录，而没有后续的"I"记录.仅使用历史记录表，可以将其转换为:

This question is, indeed, quite tricky. You need a list of the entries in history where, for a given user and area, there is an 'O' record with no subsequent 'I' record. Working with just the history table, that translates to:

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相同)，因此您需要稍加适应，并加上两张桌子:

Then, since you're really only after the person's name and the area's number (though why the area number can't be the same as its ID I am not sure), you need to adapt slightly, joining with the extra two tables:

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:

The NOT EXISTS clause is a correlated sub-query; that tends to be inefficient. You might be able to recast it as a LEFT OUTER JOIN with appropriate join and filter conditions:

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均未经验证.

All SQL unverified.