合并两个结果集 并集 带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 代替
④这里的结果集必须要设一个别名