如何借助Table1将Table2与Table3关联后插入Table4？

表结构与数据 #

表Table1 #

表Table2 #

表Table3 #

目标表Table4（期望结果） #

问题分析与修正方案 #

你的原SQL存在几个关键问题：

1. 两个子查询使用了相同的别名tt，属于语法错误
2. CROSS JOIN会产生笛卡尔积，导致重复无效数据
3. 子查询中引入Table4会重复插入已有数据，完全没必要
4. 字段名错误：Table2/Table3的日期字段是Date，不是你写的Indate/Outdate

以下是两种可行的正确实现方式：

方式1：通过关联字段左连接合并数据 #

INSERT INTO Table4 (ID, Name, Class, Date, Intime, Outtime, INAM, OUTPM)
SELECT 
    COALESCE(t2.ID, t3.ID, t1.ID) AS ID,
    COALESCE(t2.Name, t3.Name, t1.Name) AS Name,
    COALESCE(t2.Class, t3.Class, t1.Class) AS Class,
    COALESCE(t2.Date, t3.Date) AS Date,
    t2.Intime,
    t3.Outtime,
    t2.INAM,
    t3.OUTPM
FROM Table1 t1
LEFT JOIN Table2 t2 
    ON t1.ID = t2.ID AND t1.Name = t2.Name AND t1.Class = t2.Class
LEFT JOIN Table3 t3 
    ON t1.ID = t3.ID AND t1.Name = t3.Name AND t1.Class = t3.Class
    AND t2.Date = t3.Date -- 确保匹配同一天的考勤记录
WHERE COALESCE(t2.ID, t3.ID) IS NOT NULL; -- 只插入有考勤记录的行

方式2：先合并再聚合（适合多日多记录场景） #

INSERT INTO Table4 (ID, Name, Class, Date, Intime, Outtime, INAM, OUTPM)
SELECT 
    t1.ID,
    t1.Name,
    t1.Class,
    agg.Date,
    agg.Intime,
    agg.Outtime,
    agg.INAM,
    agg.OUTPM
FROM Table1 t1
INNER JOIN (
    SELECT 
        ID, Name, Class, Date,
        MAX(Intime) AS Intime,
        MAX(Outtime) AS Outtime,
        MAX(INAM) AS INAM,
        MAX(OUTPM) AS OUTPM
    FROM (
        -- 合并Table2和Table3的所有考勤字段
        SELECT ID, Name, Class, Date, Intime, NULL AS Outtime, INAM, NULL AS OUTPM FROM Table2
        UNION ALL
        SELECT ID, Name, Class, Date, NULL AS Intime, Outtime, NULL AS INAM, OUTPM FROM Table3
    ) AS combined
    GROUP BY ID, Name, Class, Date -- 按用户+日期聚合合并字段
) AS agg ON t1.ID = agg.ID AND t1.Name = agg.Name AND t1.Class = agg.Class;

执行任意一种方式后，查询Table4就能得到你期望的合并结果。

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