MySQL - 查询 Order By 没有按预期工作

Question

我期望生成的“user_id”是与 Location 表中按降序排列的第一条记录相对应的那个。但是我得到了 ASCending 顺序/或 ID 顺序中第一条记录的“user_id”。

SELECT Device.name, Location.user_id
  FROM    locations Location
       LEFT JOIN devices Device
            ON Device.id = Location.device_id
 WHERE Device.id IN (491)
GROUP BY Location.device_id
ORDER BY Location.location_datetime DESC

有人知道为什么吗？如何改进？

谢谢!

Answer 1

你可以使用这样的东西:

SELECT
  devices.name,
  (select locations.user_id
   from locations
   where locations.device_id = devices.id
   order by locations.location_datetime desc
   limit 1)
FROM
  devices
WHERE
  device.id = 491

您不能对非聚合列使用 group by 来获取第一条或最后一条记录:不能保证它始终有效。

编辑:如果您需要从位置中选择多个字段，您可以使用:

SELECT l1.*, devices.name
FROM
  locations l1 inner join
  (select device_id, max(location_datetime) as maxdt
   from locations
   group by device_id) l2
  on l1.device_id = l2.device_id and l1.location_datetime=l2.maxdt
  left join devices
  on devices.id = l1.device_id

此查询将为每个 device_id 选择最后一个位置。如果有多个位置具有相同的最大日期时间，则此查询将返回每个位置。如果您只需要一个，并且不介意选择哪个，您还可以在末尾添加一个 GROUP BY l1.device_id 子句。