如何无需子查询实现SQL关联查询，获取用户最新出行及赛事信息？

数据库背景 #

• Person表与多个Event表为一对多关联
• Event表缺失personId字段
• Travel、playsBasketball、playsFootball均为Event的子类型，继承Event的主键id

需求 #

编写SQL查询返回每个用户的两项信息：

1. 最新出行地点（该用户最后一条Travel事件的地点）
2. 最新赛事类型（该用户最后一条篮球/足球事件的类型）

同时确认：除反规范化表之外，是否存在无需使用子查询的实现方式？

示例数据 #

Person(1)
Person(2)
Event(1, 20001001)  -- 字段：id, date
Travel(1, "Barcelona")  -- 字段：event_id, location
Event(2, 20011001)
Travel(2, "Paris")
Event(3, 2041001)
Travel(3, "Girona")
Event(4, 20001001)
Travel(4, "Barcelona")

（注：示例中Person(1)关联Event(1)、(3)、(4)，Person(2)无关联事件）

预期结果 #

person_id, latest_travel_event_id, latest_travel_location
1, 4, Barcelona
2, null, null

解决方案 #

核心前提 #

首先需明确Person与Event的关联规则——由于Event表无personId，需补全该关联逻辑（比如子类型表是否包含personId？示例默认子类型表关联Person，实际业务中按真实关联关系调整JOIN条件即可）。

无需子查询的实现方式（窗口函数+DISTINCT） #

如果你的数据库支持窗口函数（如MySQL 8+、PostgreSQL、SQL Server），完全可以不用子查询，直接通过FIRST_VALUE窗口函数+DISTINCT实现：

SELECT DISTINCT
    p.id AS person_id,
    -- 获取最新出行事件的ID和地点
    FIRST_VALUE(t.event_id) OVER (
        PARTITION BY p.id 
        ORDER BY e.date DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS latest_travel_event_id,
    FIRST_VALUE(t.location) OVER (
        PARTITION BY p.id 
        ORDER BY e.date DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS latest_travel_location,
    -- 获取最新赛事类型
    FIRST_VALUE(
        CASE 
            WHEN pb.event_id IS NOT NULL THEN 'Basketball'
            WHEN pf.event_id IS NOT NULL THEN 'Football'
            ELSE NULL 
        END
    ) OVER (
        PARTITION BY p.id 
        ORDER BY CASE WHEN pb.event_id IS NOT NULL OR pf.event_id IS NOT NULL THEN e.date ELSE NULL END DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS latest_game_type
FROM Person p
-- 补充Person与Event的关联条件，示例假设子类型表通过personId关联Person
LEFT JOIN Travel t ON t.person_id = p.id
LEFT JOIN Event e ON e.id = t.event_id
LEFT JOIN playsBasketball pb ON pb.person_id = p.id AND pb.event_id = e.id
LEFT JOIN playsFootball pf ON pf.person_id = p.id AND pf.event_id = e.id;

逻辑说明 #

1. PARTITION BY p.id按用户分组，确保每个用户的事件单独排序
2. ORDER BY e.date DESC按事件日期倒序，FIRST_VALUE取每组的第一个值，即最新事件
3. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING确保窗口包含该用户的所有事件，避免默认窗口范围的限制
4. DISTINCT去重，过滤掉同一用户的多条事件记录，最终仅保留一条用户汇总数据

若数据库不支持窗口函数，确实难以避免子查询，但当前主流数据库均支持该方案，完全符合「无需子查询」的要求。

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