我有以下表格：
tblPerson：
tblPerson：
PersonID | Name --------------------- 1 | John Smith 2 | Jane Doe 3 | David Hoshi
tblLocation：
LocationID | Timestamp | PersonID | X | Y | Z | More Columns...
---------------------------------------------------------------
40 | Jan. 1st | 3 | 0 | 0 | 0 | More Info...
41 | Jan. 2nd | 1 | 1 | 1 | 0 | More Info...
42 | Jan. 2nd | 3 | 2 | 2 | 2 | More Info...
43 | Jan. 3rd | 3 | 4 | 4 | 4 | More Info...
44 | Jan. 5th | 2 | 0 | 0 | 0 | More Info...
SELECT LocationID,Timestamp,Name,X,Y,Z FROM tblLocation JOIN tblPerson ON tblLocation.PersonID = tblPerson.PersonID;
产生以下内容：
LocationID | Timestamp | Name | X | Y | Z |
--------------------------------------------------
40 | Jan. 1st | David Hoshi | 0 | 0 | 0 |
41 | Jan. 2nd | John Smith | 1 | 1 | 0 |
42 | Jan. 2nd | David Hoshi | 2 | 2 | 2 |
43 | Jan. 3rd | David Hoshi | 4 | 4 | 4 |
44 | Jan. 5th | Jane Doe | 0 | 0 | 0 |
我的问题是我们只关心最近的位置记录.因此,我们只对以下行感兴趣：LocationID 41,43和44.
问题是：我们如何查询这些表格,以便我们为每个人提供最新的数据？需要什么特殊的分组来产生所需的结果？
解决方法
MysqL没有排名/分析/窗口功能.
SELECT tl.locationid,tl.timestamp,tp.name,Z
FROM tblPerson tp
JOIN tblLocation tl ON tl.personid = tp.personid
JOIN (SELECT t.personid,MAX(t.timestamp) AS max_date
FROM tblLocation t
GROUP BY t.personid) x ON x.personid = tl.personid
AND x.max_date = tl.timestamp
sql Server 2005和Oracle 9i支持分析,因此您可以使用：
SELECT x.locationid,x.timestamp,x.name,x.X,x.Y,x.Z
FROM (SELECT tl.locationid,Z,ROW_NUMBER() OVER (PARTITION BY tp.name ORDER BY tl.timestamp DESC) AS rank
FROM tblPerson tp
JOIN tblLocation tl ON tl.personid = tp.personid) x
WHERE x.rank = 1
在MysqL上使用一个与ROW_NUMBER功能相同的变量：
SELECT x.locationid,CASE
WHEN @name != t.name THEN
@rownum := 1
ELSE @rownum := @rownum + 1
END AS rank,@name := tp.name
FROM tblLocation tl
JOIN tblPerson tp ON tp.personid = tl.personid
JOIN (SELECT @rownum := NULL,@name := '') r
ORDER BY tp.name,tl.timestamp DESC) x
WHERE x.rank = 1
