【代码笔记】【MySQL】 合并两个结果集

合并两个结果集 并集 带group by

SELECT 
field1,field2，field3   --①
FROM 
(
        SELECT 
        a.field1,b.field2,SUM(c.field3) AS field3  --②
        FROM
        table1 AS a 
        LEFT OUTER JOIN table2 AS b ON a.id = b.aid
        LEFT OUTER JOIN table3 AS c  ON c.user_id = u.id
        WHERE c.is_effect=0
        GROUP BY a.id

    UNION ALL

        SELECT 
        a.field1,b.field2,0 AS field3  --③
        FROM
        table1 AS a 
        LEFT OUTER JOIN table2 AS b ON a.id = b.aid
        LEFT OUTER JOIN table3 AS c  ON c.user_id = u.id
        GROUP BY a.id
 )  AS  table   --④

GROUP BY id

①需一一列出每一个要查询的字段 不能用 * 号
②③查询的字段必须相同 不要的结果 用 0 AS field 代替
④这里的结果集必须要设一个别名