mysql 中几个列的值相加_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