sql-server – 检索查询中的最新记录

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...

我可以生成一个SQL查询,获取每个人的位置记录,如下所示：

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.

问题是：我们如何查询这些表格,以便我们为每个人提供最新的数据？需要什么特殊的分组来产生所需的结果？

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!