检索查询中的最新记录 [英] Retrieving the most recent records within a query

问题描述

我有以下表格：


tblPerson：

  PersonID |名称
 --------------------- 
 1 |约翰史密斯
 2 | Jane Doe 
 3 | David Hoshi 
  

tbl位置：

  LocationID |时间戳| PersonID | X | Y | Z |更多列... 
 ----------------------------------------- ---------------------- 
 40 | 1月1日| 3 | 0 | 0 | 0 |更多信息... 
 41 | 1月2日| 1 | 1 | 1 | 0 |更多信息... 
 42 | 1月2日| 3 | 2 | 2 | 2 |更多信息... 
 43 | 1月3日| 3 | 4 | 4 | 4 |更多信息... 
 44 | 1月5日| 2 | 0 | 0 | 0 |更多信息... 
  

我可以生成一个SQL查询，获取每个人的位置记录，例如：

  SELECT LocationID，Timestamp，Name，X，Y，Z 
 FROM tblLocation 
 JOIN tblPerson 
 ON tblLocation.PersonID = tblPerson.PersonID; 
  

生成以下内容：

  LocationID |时间戳|名称| X | Y | Z | 
 ---------------------------------------------- ---- 
 40 | 1月1日| David Hoshi | 0 | 0 | 0 | 
 41 | 1月2日|约翰史密斯| 1 | 1 | 0 | 
 42 | 1月2日| David Hoshi | 2 | 2 | 2 | 
 43 | 1月3日| David Hoshi | 4 | 4 | 4 | 
 44 | 1月5日| Jane Doe | 0 | 0 | 0 | 
  

我的问题是我们只关注最近的位置记录。因此，我们只对以下行感兴趣：LocationID 41,43和44。

问题是：：怎么可以我们查询这些表格给我们最新的每个人的数据？什么特殊的分组需要发生，以产生所需的结果？ 解决方案

MySQL没有排名/分析/窗口功能。 / p>

  SELECT tl.locationid，tl.timestamp，tp.name，X，Y，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，xX，xY，xZ 
 FROM（SELECT tl.locationid，tl.timestamp，tp.name，X，Y，Z，
 ROW_NUMBER（）OVER（PARTITION BY tp.name ORDER BY tl.timestamp DESC）AS等级
从tblPerson tp 
加入tblLocation tl.personid = tp.personid）x 
 WHERE x.rank = 1 
  

使用变量在MySQL上获得与ROW_NUMBER相同的功能：

  SELECT x.locationid，x.timestamp，x.name，xX ，xY，xZ 
 FROM（SELECT tl.locationid，tl.timestamp，tp.name，X，Y，Z，
 CASE 
 WHEN @name！= t.name THEN 
 @rownum：= 1 
 ELSE @rownum：= @rownum + 1 
 END AS等级，
 @name：= tp.name 
从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 
  

I have the following tables:

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

I can produce an SQL query that gets the Location records for each Person like so:

SELECT LocationID, Timestamp, Name, X, Y, Z 
FROM tblLocation 
JOIN tblPerson 
ON tblLocation.PersonID = tblPerson.PersonID;

to produce the following:

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 |

My issue is that we're only concerned with the most recent Location record. As such, we're only really interested in the following Rows: LocationID 41, 43, and 44.

The question is: How can we query these tables to give us the most recent data on a per-person basis? What special grouping needs to happen to produce the desired result?

解决方案

MySQL doesn't have ranking/analytical/windowing functionality.

SELECT tl.locationid, tl.timestamp, tp.name, X, Y, 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+ and Oracle 9i+ support analytics, so you could use:

SELECT x.locationid, x.timestamp, x.name, x.X, x.Y, x.Z
  FROM (SELECT tl.locationid, tl.timestamp, tp.name, X, Y, 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

Using a variable to get same as ROW_NUMBER functionality on MySQL:

SELECT x.locationid, x.timestamp, x.name, x.X, x.Y, x.Z
  FROM (SELECT tl.locationid, tl.timestamp, tp.name, X, Y, Z,
               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

这篇关于检索查询中的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！