SQL:从历史记录表中获取最新条目 [英] SQL: Get latest entries from history table

问题描述

我有3张桌子

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

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

In this tables I store the info which person had which area at a specific time. It is like a salesman travels in an area for a while and then he gets another area. He can also have multiple areas at a time.

历史记录类型="I"表示签入，"O"表示签出. 示例:

history type = 'I' for CheckIn or 'O' for Checkout. Example:

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.

现在我想列出所有人员现在拥有的所有区域.

Now I want to have a list of all the areas all persons have right now.

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

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

The output could be like this too (it doesn't matter):

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

我该怎么做?

推荐答案

这个问题确实很棘手.您需要历史记录中的条目列表，其中对于给定的用户和区域，存在"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.

这篇关于SQL:从历史记录表中获取最新条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！