mysql - 如何对mysql中的多个不同表求和

Question

我有 3 个查询

select uid, sum(call_duration) as cTime from privacy_call_history 
where event_date >= '2015-11-20 01:00:00' and event_date < '2015-11-20 01:00:00' + interval 1 hour
group by uid having sum(call_duration) > 0;

select uid, sum(run_time) as rTime from app_finish_history
where event_date >= '2015-11-20 01:00:00' and event_date < '2015-11-20 01:00:00' + interval 1 hour
group by uid having sum(run_time) > 0;

select uid, sum(chat_time) as wcTime, sum(msg_avg_time) as maTime from whisper_chat_history
where event_date >= '2015-11-20 01:00:00' and event_date < '2015-11-20 01:00:00' + interval 1 hour
group by uid having sum(chat_time) > 0 and sum(msg_avg_time) > 0;

我想将它们组合成一个结果集，如下所示:

uid	     cTime		      rTime		     wcTime	            maTime
1		2			0			0			0
2		0			3			0			0
3		0			0			4			0
4		0			0			0			5

如何查询？

Answer 1

您可以将查询作为子查询合并到单个结果集中。但是，您要么需要包含完整 uid 列表的用户表，要么需要在 mysql 中模拟完整的外连接。我会使用用户表(或任何名称)。

select user.uid, ifnull(t1.cTime,0) cTime, ifnull(t2.rTime,0) rTime, ifnull(t3.wcTime,0) wcTime, ifnull(t3.maTime,0) maTime
from user left join
(select uid, sum(call_duration) as cTime from privacy_call_history 
where event_date >= '2015-11-20 01:00:00' and event_date < '2015-11-20 01:00:00' + interval 1 hour
group by uid having sum(call_duration) > 0) t1 on user.uid=t1.uid
left join
(select uid, sum(run_time) as rTime from app_finish_history
where event_date >= '2015-11-20 01:00:00' and event_date < '2015-11-20 01:00:00' + interval 1 hour
group by uid having sum(run_time) > 0) t2 on user.uid=t2.uid
left join
(select uid, sum(chat_time) as wcTime, sum(msg_avg_time) as maTime from whisper_chat_history
where event_date >= '2015-11-20 01:00:00' and event_date < '2015-11-20 01:00:00' + interval 1 hour
group by uid having sum(chat_time) > 0 and sum(msg_avg_time) > 0) t3 on user.uid=t3.uid

如果要过滤掉那些未出现在任何子选择中的用户，请添加一个要求至少 1 个非空值的条件:coalesce(cTime, rTime, wcTime, maTime) is not null