join关联表子查询提示没有为DetailX的列2指定任何列名称原因。

简书链接:join关联表子查询提示没有为DetailX的列2指定任何列名称原因。
文章字数：264,阅读全文大约需要1分钟
刚开始以为 不支持提前汇总，后面发现需要给汇总的列 指定别名， 低级错误

SELECT 
    A.*, 
    B.name, 
    AVG(B.col2) AS avg_col2, 
    SUM(B.col3) AS sum_col3, 
    C.start_time, 
    C.end_time
FROM 
    A
LEFT JOIN (
    SELECT 
        name, 
        col2, 
        col3 
    FROM 
        B
    WHERE 
        EXISTS (
            SELECT 1 
            FROM C 
            WHERE B.insert_time BETWEEN C.start_time AND C.end_time
        )
    GROUP BY 
        name, 
        col2, 
        col3
) AS B ON A.sid = B.sid
LEFT JOIN C ON A.cid = C.cid
-- 添加其他JOIN的表
GROUP BY 
    A.sid, 
    A.col1, 
    A.col2, 
    A.col3, 
    B.name, 
    C.start_time, 
    C.end_time;

假设B表有10个name和a匹配，我只需要一个， 但是b表所有name的数据我都要，就要提前汇总，不然会出现重复数据影响整体计算
提前汇总

SELECT 
    A.*, 
    B.name, 
    B.avg_col2, 
    B.sum_col3, 
    C.start_time, 
    C.end_time
FROM 
    A
INNER JOIN (
    SELECT 
        name, 
        AVG(col2) AS avg_col2, 
        SUM(col3) AS sum_col3
    FROM 
        (
            SELECT 
                B1.name, 
                B1.col2, 
                B1.col3 
            FROM 
                B AS B1
            INNER JOIN C AS C1 ON B1.insert_time BETWEEN C1.start_time AND C1.end_time
        ) AS B_filtered
    GROUP BY 
        name
) AS B ON A.sid = B.name -- 假设name是关联键
LEFT JOIN C ON A.cid = C.cid
-- 添加其他JOIN的表
GROUP BY 
    A.sid, 
    A.col1, 
    A.col2, 
    A.col3, 
    B.name, 
    B.avg_col2, 
    B.sum_col3, 
    C.start_time, 
    C.end_time;

但是如果要根据外部c表关联汇总， 几个name对应一个总数 ，和外部的c表关联是做不到了，只能在子查询里面再关联，外部再关联