如何在嵌套查询中插入左连接？并为学生移动查询添加firstGateName与LastGateName

先结合需求推测下三张表的核心结构（方便后续SQL示例对齐）：

• Table1（学生表）：含student_id等学生唯一标识字段
• Table2（移动记录表）：含student_id、move_time（移动时间）、gate_id（关联门的ID）字段
• Table3（门信息表）：含gate_id、gate_name（门名称）字段

问题1：在嵌套查询中插入左连接，确保无移动日期返回null #

核心思路是先构建「指定日期区间内所有日期 + 所有学生」的完整维度表，再左连接到聚合后的移动记录——这样就能保证哪怕某天学生没有移动，也能生成对应null值的记录。

给你一个可直接参考的SQL示例：

-- 1. 生成指定日期区间的所有日期
WITH date_range AS (
    SELECT generate_series(
        '2024-01-01'::DATE, 
        '2024-01-31'::DATE, 
        '1 day'::INTERVAL
    ) AS record_date
),
-- 2. 生成学生+日期的完整维度（确保每个学生每天都有一条基础记录）
student_daily_dim AS (
    SELECT s.student_id, dr.record_date
    FROM Table1 s
    CROSS JOIN date_range dr
),
-- 3. 聚合每个学生每天的首次/末次移动时间、对应门ID
daily_move_agg AS (
    SELECT 
        student_id,
        DATE(move_time) AS record_date,
        MIN(move_time) AS first_move_time,
        MAX(move_time) AS last_move_time,
        -- 用窗口函数抓取首次移动对应的门ID
        FIRST_VALUE(gate_id) OVER (PARTITION BY student_id, DATE(move_time) ORDER BY move_time) AS first_gate_id,
        -- 用窗口函数抓取末次移动对应的门ID（注意加RANGE范围，避免窗口默认限制）
        LAST_VALUE(gate_id) OVER (PARTITION BY student_id, DATE(move_time) ORDER BY move_time RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_gate_id
    FROM Table2
    GROUP BY student_id, DATE(move_time), gate_id, move_time
)
-- 4. 左连接维度表和聚合表，无移动日期自动返回null
SELECT 
    sdd.student_id,
    sdd.record_date,
    dma.first_move_time,
    dma.last_move_time,
    dma.first_gate_id,
    dma.last_gate_id
FROM student_daily_dim sdd
LEFT JOIN daily_move_agg dma 
    ON sdd.student_id = dma.student_id 
    AND sdd.record_date = dma.record_date
ORDER BY sdd.student_id, sdd.record_date;

如果你的数据库是MySQL（不支持generate_series），可以把date_range替换成递归CTE生成日期：

-- MySQL版本的日期生成逻辑
date_range AS (
    SELECT '2024-01-01' AS record_date
    UNION ALL
    SELECT DATE_ADD(record_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE record_date < '2024-01-31'
)

问题2：添加firstGateName和lastGateName字段 #

只需要在上述查询基础上，左连接两次Table3，分别关联首次、末次移动的门ID即可拿到对应名称。

修改后的完整SQL：

WITH date_range AS (
    SELECT generate_series(
        '2024-01-01'::DATE, 
        '2024-01-31'::DATE, 
        '1 day'::INTERVAL
    ) AS record_date
),
student_daily_dim AS (
    SELECT s.student_id, dr.record_date
    FROM Table1 s
    CROSS JOIN date_range dr
),
daily_move_agg AS (
    SELECT 
        student_id,
        DATE(move_time) AS record_date,
        MIN(move_time) AS first_move_time,
        MAX(move_time) AS last_move_time,
        FIRST_VALUE(gate_id) OVER (PARTITION BY student_id, DATE(move_time) ORDER BY move_time) AS first_gate_id,
        LAST_VALUE(gate_id) OVER (PARTITION BY student_id, DATE(move_time) ORDER BY move_time RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_gate_id
    FROM Table2
    GROUP BY student_id, DATE(move_time), gate_id, move_time
)
SELECT 
    sdd.student_id,
    sdd.record_date,
    dma.first_move_time,
    dma.last_move_time,
    g1.gate_name AS firstGateName,
    g2.gate_name AS lastGateName
FROM student_daily_dim sdd
LEFT JOIN daily_move_agg dma 
    ON sdd.student_id = dma.student_id 
    AND sdd.record_date = dma.record_date
-- 左连接Table3获取首次移动的门名称
LEFT JOIN Table3 g1 
    ON dma.first_gate_id = g1.gate_id
-- 左连接Table3获取末次移动的门名称
LEFT JOIN Table3 g2 
    ON dma.last_gate_id = g2.gate_id
ORDER BY sdd.student_id, sdd.record_date;

小提示 #

如果你的原有查询已经有成熟的聚合逻辑，只需要把它替换到daily_move_agg部分，再和student_daily_dim左连接就能实现无移动日期返回null的需求；窗口函数的用法能避免多次子查询扫描表，提升查询性能。

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