MYSQL实现将两个结果集合并,并且按照时间字段分组,其他字段的值求和

本查询是计算两个表的相同时间(天)的记录数

1.合并就用UNION ALL

第一个查询:

SELECT
DATE_FORMAT(BIND_TIME,'%Y-%m-%d') time,
count(*)
FROM bind_hist
GROUP BY time

第二个查询:

SELECT 
DATE_FORMAT(BIND_TIME,'%Y-%m-%d') time,
count(*)
FROM bind_failed 
GROUP BY time

合并后为:

SELECT
	DATE_FORMAT(BIND_TIME,'%Y-%m-%d') time,
	0 as failed,
	count(*) hist
FROM bind_hist
GROUP BY time
UNION ALL
SELECT 
	DATE_FORMAT(BIND_TIME,'%Y-%m-%d') time,
	count(*) failed,
	0
FROM bind_failed 
GROUP BY time

这里使用0 as failed作用是可以展示第二个查询的数值,因为UNION ALL本身只显示第一个查询的列

2.将合并后的结果作为一个表,再根据时间GROUP BY,求和值

SELECT 
	r.time,
	SUM(r.failed),
	SUM(r.hist) 
FROM (
	SELECT
		DATE_FORMAT(BIND_TIME,'%Y-%m-%d') time,
		0 as failed,
		count(*) hist
	FROM bind_hist
	GROUP BY time
	UNION ALL
	SELECT 
		DATE_FORMAT(BIND_TIME,'%Y-%m-%d') time,
		count(*) failed,
		0
	FROM bind_failed 
	GROUP BY time
) r
GROUP BY r.time