mysql 合并两个查询结果

  合并两个查询结果

查询结果1：

select date(complete_time) as time,sum(amt) as amount_outfrom withdrawwhere state=3group by date(complete_time)

查询结果2

select date(update_time) as time,sum(transfer_amount) as amount_infrom user_chargewhere status=3group by date(update_time)

显然，查询结果的time为主键，目的是将两个查询结果合成一个结果。如果在代码中实现，一次便利一个表添加到另外一个表中，效率非常低。那么在Mysql 中实现方式为：

<span style="font-size:18px;">-- 出金 withdrawselect * from (select date(complete_time) as time,sum(amt) as amount_out,0 as amount_infrom withdrawwhere state=3group by date(complete_time)union all -- 入金 select date(update_time) as time,0 as amount_out,sum(transfer_amount) as amount_infrom user_chargewhere status=3group by date(update_time)) agroup by time</span>

使用关键词Union all 。注意：两个列表中的字段要一样才可以合并(顺序也要一样)

查询结果：