问题描述
我有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:从历史记录表中获取最新条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!