如何实现多表连接查询并仅返回两行符合要求的目标结果

实现方案 #

你之前使用INNER JOIN直接关联两张表会返回一人对应多车的多条记录，直接搭配GROUP BY未做合理聚合也无法得到预期的2条结果，可根据你使用的数据库版本选择以下两种实现方式：

方式1：窗口函数实现（支持MySQL 8.0+、PostgreSQL、SQL Server等主流新版本数据库） #

先对车辆表按人员ID分组排序，取每个人员下排序第一的车辆记录再和人员表关联：

SELECT p.*, c.brand, c.purchase_date
FROM Person p
INNER JOIN (
    SELECT 
        *,
        -- 同人员下按购车日期倒序排序，取最新的一辆，可根据实际需求调整排序规则
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY purchase_date DESC) AS rn
    FROM Car
) c ON p.id = c.person_id
WHERE c.rn = 1;

方式2：聚合子查询实现（兼容MySQL 5.x等不支持窗口函数的低版本数据库） #

先查询每个人员对应的符合要求的车辆维度值，再关联全表得到完整信息：

SELECT p.*, c.brand, c.purchase_date
FROM Person p
INNER JOIN Car c ON p.id = c.person_id
INNER JOIN (
    -- 取每个人员最新的购车日期，可根据需求调整聚合逻辑
    SELECT person_id, MAX(purchase_date) AS latest_purchase_date
    FROM Car
    GROUP BY person_id
) t ON c.person_id = t.person_id AND c.purchase_date = t.latest_purchase_date;

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