SQL如何连接多个多对一关联表并查询各层级最新记录

云上老周

2026-8-28

问题说明

你当前写的SQL存在语法错误：WHERE后面对应最大日期的子查询没有用括号包裹，无法正常执行
仅通过date = MAX(date)匹配Tracker记录存在逻辑漏洞：如果同一设备下存在多条date完全相同的Tracker记录，会导致单设备返回多行重复结果
现有逻辑只覆盖了关联最新Tracker的部分，没有实现「拿到最新Tracker后，再关联其下最新Location」的二级关联需求
直接按tracker_id分组属于聚合维度选择错误，会打散设备维度的聚合结果，自然会返回单设备下所有Tracker记录，不符合预期

实现方案

从你用public schema的写法判断你用的是PostgreSQL，优先用DISTINCT ON语法实现，写法简洁、执行效率高：

SELECT
  d.*,
  -- 替换成你实际需要的users表指定字段，不要直接用u.*避免同名字段冲突
  u.username,
  u.user_type,
  t.id AS latest_tracker_id,
  t.date AS latest_tracker_date,
  l.id AS latest_location_id,
  l.date AS latest_location_date
  -- 按需补充Tracker、Location表需要返回的其他字段
FROM public.devices d
JOIN public.users u
  ON u.id = d.user_id
-- 关联每个设备下最新的Tracker记录
LEFT JOIN (
  SELECT DISTINCT ON (device_id)
    id,
    device_id,
    date
    -- 按需补充Tracker表其他需要的字段
  FROM public.trackers
  -- 按设备分组，日期倒序取第一条，加id倒序兜底避免同日期多条记录时结果不确定
  ORDER BY device_id, date DESC, id DESC
) t ON t.device_id = d.id
-- 关联每个最新Tracker下的最新Location记录
LEFT JOIN (
  SELECT DISTINCT ON (tracker_id)
    id,
    tracker_id,
    date
    -- 按需补充Location表其他需要的字段
  FROM public.locations
  ORDER BY tracker_id, date DESC, id DESC
) l ON l.tracker_id = t.id
ORDER BY d.id DESC;

如果你用的是MySQL等不支持DISTINCT ON的数据库，可以换用窗口函数ROW_NUMBER()实现，逻辑完全等价：

SELECT
  d.*,
  u.username,
  u.user_type,
  t.id AS latest_tracker_id,
  t.date AS latest_tracker_date,
  l.id AS latest_location_id,
  l.date AS latest_location_date
FROM public.devices d
JOIN public.users u
  ON u.id = d.user_id
LEFT JOIN (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY date DESC, id DESC) AS rn
  FROM public.trackers
) t ON t.device_id = d.id AND t.rn = 1
LEFT JOIN (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY tracker_id ORDER BY date DESC, id DESC) AS rn
  FROM public.locations
) l ON l.tracker_id = t.id AND l.rn = 1
ORDER BY d.id DESC;

注意事项

两个关联子查询的排序规则里都加了id DESC作为兜底条件，避免同一时间戳下存在多条记录时，返回结果不确定
不要直接用SELECT *关联多表，建议显式指定需要返回的字段，避免不同表存在id/date等同名字段时出现数据覆盖问题
上述写法用LEFT JOIN是为了兼容「设备无Tracker」「Tracker无Location」的场景，这类场景下对应Tracker、Location字段会返回NULL；如果业务要求必须存在Tracker和Location才返回设备记录，把LEFT JOIN换成INNER JOIN即可

内容的提问来源于stack exchange，提问作者Supermaniac