单查询跨三表获取多ID对应max()值的SQL问题排查

原SQL无法返回预期结果的核心问题有2个：

• 聚合逻辑错误：子查询select max(time_start) from session未按id_client分组，返回的是整张session表的全局最大开始时间，最终只会匹配到全局时间最大的单条记录，无法为每个符合条件的客户返回各自的最大时间。
• 采用逗号分隔的隐式表连接写法，可读性差，后续维护时容易漏写连接条件产生笛卡尔积，推荐使用显式JOIN写法。

通用兼容写法（所有SQL版本均支持） #

先在子查询中按id_client分组计算每个客户对应的最大开始时间，再关联另外两张表过滤名称条件：

SELECT
  ts.max_time AS time_start,
  c.id_client,
  n.name
FROM
  client c
  INNER JOIN name n ON c.id_deck = n.ID_DECK
  INNER JOIN (
    SELECT
      id_client,
      MAX(time_start) AS max_time
    FROM session
    GROUP BY id_client
  ) ts ON c.id_client = ts.id_client
WHERE
  n.name LIKE '%John Doe%'

窗口函数写法（支持MySQL8.0+、PostgreSQL、SQL Server等主流数据库新版本） #

用窗口函数给每个客户的session记录按开始时间倒序排序，直接取每个客户排序第一的记录即可，不需要单独分组：

SELECT
  ts.time_start,
  c.id_client,
  n.name
FROM
  (
    SELECT
      id_client,
      time_start,
      ROW_NUMBER() OVER (
        PARTITION BY id_client
        ORDER BY time_start DESC
      ) AS time_rank
    FROM session
  ) ts
  INNER JOIN client c ON ts.id_client = c.id_client
  INNER JOIN name n ON c.id_deck = n.ID_DECK
WHERE
  n.name LIKE '%John Doe%'
  AND ts.time_rank = 1

注意：如果同一个id_client下存在多条time_start完全相同且同为最大值的记录，ROW_NUMBER()会随机返回其中一条，需要保留所有同值最大记录可以替换为RANK()函数。

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