代码之家 › 专栏 › 技术社区 › Beau Simensen

按查询指定组的排序顺序,以检索每个组的最旧或最新记录

group-by mysql sql

Beau Simensen · 技术社区 · 15 年前

我需要从升级请求日志表中获取每个设备的最新记录。基于硬件id和mac地址的组合,设备是唯一的。我一直在尝试这样做 GROUP BY 但我不相信这是安全的,因为它看起来可能只是返回“top record”(不管sqlite或mysql认为是什么)。

我曾希望这个“最高纪录”可以通过 ORDER BY 但这似乎没有任何影响,因为以下两个查询按相反的顺序为每个设备返回相同的记录:

SELECT extHwId,
       mac,
       created
  FROM upgradeRequest
 GROUP BY extHwId, mac
 ORDER BY created DESC

SELECT extHwId,
       mac,
       created
  FROM upgradeRequest
 GROUP BY extHwId, mac
 ORDER BY created ASC

有没有其他方法来实现这个目标?我看过一些相关的帖子,都涉及到了子选择。如果可能的话,我想做这个没有子选择,因为我想学习如何做这个没有那个。

3 回复 | 直到 15 年前

David Neale 15 年前

尝试:

 SELECT extHwId, mac, MAX(created)
 FROM upgradeRequest
 GROUP BY extHwId, mac

Daniel Renshaw 15 年前

你不能“得到最新的记录 “使用分组方式。GROUPBY将许多记录聚合在一起,因此您最终看到/提取的不是表中的实际记录,而是从一个或多个表记录构造的“虚拟”记录。

如果您真的想要每个设备的最新记录,则需要使用子查询。但是,如果只想知道每个设备的最新记录的日期,则可以通过在“已创建”字段周围放置最大聚合来使用GROUP:

SELECT
    extHwId, 
    mac, 
    MAX(created)
FROM upgradeRequest
GROUP BY extHwId, mac
ORDER BY created ASC

Philip Kelley 15 年前

这应该可以…

SELECT ur.extHwId,
       ur.mac,
       ur.created
 from upgradeRequest ur
  left outer join upgradeRequest ur2
   on ur2.extHwId = ur.extHwId
    and ur2.mac = ur.mac
    and ur2.Created > ur.Created  --  Join with all "later" entries
 where ur2.Created is null  --  Filter out all rows that have any later entries

…但这很尴尬,可能在大型表上执行得不好(因为您几乎每行都在阅读和检查),如果有多个条目设置了完全相同的最新日期,则会产生重复项。使用子查询(如以下表单)时,这种查询的效率会更高:

SELECT ur.extHwId,
       ur.mac,
       ur.created
 from upgradeRequest ur
 where not exists (select 1
                    from upgradeRequest ur2
                    where ur2.extHwId = ur.extHwId
                     and ur2.mac = ur.mac
                     and ur2.Created > ur.Created)

这里的优点是数据库引擎只需在子查询中找到一行(而不是先读取所有行)即可筛选出一行。