代码之家 › 专栏 › 技术社区 › Frank Krueger

帮助编写查询:对group by和order by操作顺序的混淆

database sql mysql

Frank Krueger · 技术社区 · 17 年前

我有一个名为“此架构的信息”的表:

int objectId;
int time;
int x, y;

系统中有很多冗余数据——也就是说, objectId 不是唯一的。对于每一个 客体 可以有多个条目 time, x, y .

我想检索每个对象最新位置的列表。我从这个问题开始:

SELECT * FROM Info GROUP BY objectId

这使我得到了我正在寻找的那种清单。不过,我也希望得到每个对象的最新时间,因此我尝试了:

SELECT * FROM Info GROUP BY objectId ORDER BY time DESC

这给了我一个 time 信息的子列表。然而, 我不认为它做了我想做的-那就是把最新的还给我 时间,x,y 对于每个对象 .

有人能想象一个查询能满足我的需求吗?

更新我尝试了前三种解决方案,以了解它们在大约50000个信息集上的表现。结果如下:

-- NO INDEX: forever
-- INDEX: 7.67 s

SELECT a.*
FROM Info AS a
  LEFT OUTER JOIN Info AS b ON (a.objectId = b.objectId AND a.time < b.time)
WHERE b.objectId IS NULL;

-- NO INDEX: 8.05 s
-- INDEX: 0.17 s

select a.objectId, a.time, a.x, a.y
  from Info a,
       (select objectId, max(time) time from Info group by objectId) b
  where a.objectId = b.objectId and a.time = b.time;

-- NO INDEX: 8.30 s
-- INDEX: 0.18 s

SELECT A.time, A.objectId, B.x, B.y
FROM
(
   SELECT max(time) as time, objectId 
   FROM Info
   GROUP by objectId
) as A 
INNER JOIN Info B
   ON A.objectId = b.objectId AND A.time = b.time;

在一定程度上,它似乎 where 表现优于 inner join .

4 回复 | 直到 12 年前

Community CDub 8 年前

一种方法是使用子查询。

select distinct a.objectID, a.time, a.x, a.y
  from Info a,
       (select objectID, max(time) time from Info group by objectID) b
  where a.objectID = b.objectID and a.time = b.time

编辑:添加distinct以防止一个objectid同时具有多个记录时出现重复行。根据您的数据,如果有必要,作者提到的问题有许多重复的行。( 附加的 Tomalak )

Eoin Campbell 17 年前

SELECT A.time, A.objectID, B.X, B.Y
FROM
(
   SELECT max(time) as time, objectID 
   FROM table
   GROUP by objectID
) as A 
INNER JOIN table B
   ON A.objectID = b.objectID AND A.Time = b.Time

如果X&Y在时间线的任何一点上递减,解决方案将不起作用。

Bill Karwin 17 年前

对于它的价值,这里有另一种方法来获得想要的结果。在支持子查询之前,我在MySQL4.0中养成了这样的习惯。

SELECT a.*
FROM Info AS a
  LEFT OUTER JOIN Info AS b ON (a.objectID = b.objectID AND a.time < b.time)
WHERE b.objectID IS NULL;

换句话说,向我展示一行,其中不存在具有相同objectid和更大时间的其他行。这自然会返回每个objectid的最大时间行。不需要分组依据。

Walter Mitty 17 年前

这是获取一行中所有信息的一种非常常见的方法,对于属于一个组的行来说。

Select Info.*
from Info
inner join
   (select ObjectId, max(time) as Latest
    from Info
    group by ObjectId)  I
on Info.ObjectId = I.ObjectID and Info.time = I.Latest

在过去的几周里,同样的问题以不同的形式被问过几次。我忘了这些问题是怎么说的。