mysql - 带聚合函数的子查询

Question

我无法获得正确的记录。我的位置表记录了一件设备的位置历史。因此，最新的 date_moved 将是其当前位置。位置表目前包含 2258 条记录(“state_tag”是 FK)。在我的设备表中，我有 2050 件设备，每件设备都有一个唯一的“state_tag”编号 (PK)。所以，我想要的是查询位置表以获取所有设备的当前位置(即 MAX(date_moved)。然后查询应该返回 2050 条记录。

按照上一篇文章的示例，我尝试了以下查询:

SELECT * FROM its_locations WHERE date_moved in (SELECT MAX(date_moved) FROM its_locations GROUP BY state_tag);

但是，查询在应该返回 2050 条记录时返回了 2257 条记录。显然，我的查询完全按照写的那样进行，但不是我们想要的(笑)。

我还尝试了以下查询，它给了我 2050 条记录，但只返回了每个组的第一条记录，并将该组的最大日期插入到该记录中，该记录很可能不是其当前位置。

SELECT state_tag
, MAX(date_moved) AS “移动日期”
， 建筑
， 房间
， 职员
， 评论
从它的位置
GROUP BY state_tag;

请帮助我进行正确的查询。

非常感谢。

克里斯

Answer 1

这是您的查询:

SELECT *
FROM its_locations
WHERE date_moved in (SELECT MAX(date_moved) FROM its_locations GROUP BY state_tag)

子查询计算所有位置的最大 date_moved。然后它返回具有这些日期的任何位置。因此，两个位置可能在同一日期移动，一个是最大日期，并且两个位置都被返回。

你想要的是一个相关的子查询，而不是group by:

SELECT l.*
FROM its_locations l
WHERE date_moved = (SELECT MAX(l2.date_moved)
                    FROM its_locations l2
                    WHERE l2.state_tag = l.state_tag
                   );

如果原始表有重复项，您可能仍会得到超过 2,050 行。但这可能会解决您的问题。