请教大神怎么解决sql中多个select合并问题

Question

SELECT months, total, ruzhu, cancels
FROM (SELECT to_char(ordertime,'mm') as months,count(*) as total, 0 ruzhu, 0 cancels
FROM tb_order where B_ID = '1'
GROUP BY to_char(ordertime,'mm')

UNION ALL

SELECT to_char(ordertime,'mm') as months, 0 total, count(*) as ruzhu, 0 cancels
FROM tb_order where B_ID = '1' and (status = 1 or status = 2 or status = 3)
GROUP BY to_char(ordertime,'mm')

UNION ALL

SELECT to_char(ordertime,'mm') as months, 0 total, 0 ruzhu, count(*) as cancels
FROM tb_order where B_ID = '1' and (status = 6 or status = 7)
GROUP BY to_char(ordertime,'mm'))
查询结果如下图

我想想要的结果是
moths total ruzhu cancels

08 1 1 0
07 7 5 2

Answer 1

把你的查询语句用select包起来，然后按月份分组
select sum(a.total) total, sum(a.ruzhu) ruzhu, sum(a.cancels) cancels from ( 你的sql ) a group by a.months