本查询是计算两个表的相同时间(天)的记录数
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