MySQL多表关联查询如何获取每个设备的最新部署记录

问题背景 #

现有一套存储设备及部署信息的数据库，设备可存放于库房，也可部署到指定地点，退回库房后可再次部署到其他位置。

表结构 #

• Devices表（设备表）

+----+---------+
|id  |serialno |
+----+---------+
|1   |serial1  |
+----+---------+
|2   |serial2  |
+----+---------+

• Deployments表（部署记录表）

+----+---------+---------+
|id  |deviceID |location |
+----+---------+---------+
|1   |1        |location1|
+----+---------+---------+
|2   |1        |location2|
+----+---------+---------+
|3   |2        |location3|
+----+---------+---------+
|4   |2        |location4|
+----+---------+---------+

现有写法的问题 #

当前尝试的两种写法都无法得到预期结果，原因如下：

1. 带DISTINCT的查询会返回全部4条关联记录：DISTINCT是对查询返回的整行数据去重，不同部署记录的location值存在差异，每一行都是唯一值，无法实现单设备仅返回一条记录的效果。原SQL如下：

   SELECT distinct devices.id as devID, serialno, location
   FROM devices INNER JOIN deployments ON devices.id = deployments.deviceID
   ORDER BY deployments.id DESC
   

2. 直接使用GROUP BY按设备ID分组，仅能返回每个设备的第一条部署记录：分组时没有指定取最新部署记录的匹配规则，数据库会默认返回分组内物理排序靠前的记录，无法拿到最新的最后一条部署数据。

预期返回结果 #

+-----+---------+---------+
|devID|serialno |location |
+-----+---------+---------+
|2    |serial2  |location4|
+-----+---------+---------+
|1    |serial1  |location2|
+-----+---------+---------+

正确写法 #

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

先通过子查询查出每个设备对应的最大部署记录ID（即最新部署的主键），再关联表取对应完整数据，兼容性最好：

SELECT 
  d.id AS devID,
  d.serialno,
  dp.location
FROM devices d
INNER JOIN (
  -- 先聚合得到每个设备的最新部署记录ID
  SELECT deviceID, MAX(id) AS latest_deploy_id
  FROM deployments
  GROUP BY deviceID
) latest_dp ON d.id = latest_dp.deviceID
INNER JOIN deployments dp ON latest_dp.latest_deploy_id = dp.id
ORDER BY d.id DESC

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

使用ROW_NUMBER()窗口函数按设备分组，按部署记录ID倒序排名，取每组排名第一的记录即可，写法更简洁易维护：

WITH ranked_deploy AS (
  SELECT
    deviceID,
    location,
    ROW_NUMBER() OVER (PARTITION BY deviceID ORDER BY id DESC) AS rn
  FROM deployments
)
SELECT
  d.id AS devID,
  d.serialno,
  rd.location
FROM devices d
INNER JOIN ranked_deploy rd ON d.id = rd.deviceID
WHERE rd.rn = 1
ORDER BY d.id DESC

提示：如果需要查询包含从未部署过、当前存放在库房的设备，将上述语句中关联部署相关表的INNER JOIN替换为LEFT JOIN即可，未部署设备的location字段会返回空值。

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