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

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



发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

ORACLE 多列合并成一行数据 WM_CONCAT函数以及REPLACE详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。