SELECT DATE_FORMAT(createtime,'%d') TIME, COUNT(*) AS authTotal FROM A WHERE DATE_FORMAT(createtime,'%Y-%m')='2015-04' GROUP BY DATE_FORMAT(createtime,'%Y-%m-%d') 结果如下:
SELECT DATE_FORMAT(TIME,'%d') TIME,COUNT(*) AS subTotal FROM B WHERE DATE_FORMAT(TIME,'%Y-%m')='2015-04' GROUP BY DATE_FORMAT(TIME,'%Y-%m-%d')结果如下:
现在想将这两个结果集合并为一个,即time, authTotal,subTotal 这三列
COUNT(*) AS authTotal, B.subTotal FROM A
WHERE DATE_FORMAT(createtime,'%Y-%m')='2015-04'
GROUP BY DATE_FORMAT(createtime,'%Y-%m-%d')
INNER JOIN (
SELECT DATE_FORMAT(TIME,'%d') AS TIMES,
COUNT(*) AS subTotal FROM B
WHERE DATE_FORMAT(TIME,'%Y-%m')='2015-04'
GROUP BY DATE_FORMAT(TIME,'%Y-%m-%d')) AS B
ON A.TIMES = B.TIMES
SELECT DATE_FORMAT(createtime,'%d') TIME, COUNT(*) AS authTotal FROM A WHERE DATE_FORMAT(createtime,'%Y-%m')='2015-04' GROUP BY DATE_FORMAT(createtime,'%Y-%m-%d')
union all
SELECT DATE_FORMAT(TIME,'%d') TIME,COUNT(*) AS subTotal FROM B WHERE DATE_FORMAT(TIME,'%Y-%m')='2015-04' GROUP BY DATE_FORMAT(TIME,'%Y-%m-%d')