56,679
社区成员
发帖
与我相关
我的任务
分享
-- 建表
CREATE TABLE `t2` (
`date` int(11) NOT NULL,
PRIMARY KEY (`date`)
)
-- 插入记录
INSERT INTO `t2` (`date`) VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10'), ('11'), ('12'), ('13'), ('14'), ('15'), ('16'), ('17'), ('18'), ('19'), ('20'), ('21'), ('22'), ('23'), ('24'), ('25'), ('26'), ('27'), ('28'), ('29'), ('30'), ('31')
2.借助表t2,把整个月份的日期拉取齐全
SELECT
DATE_ADD(CONCAT('2018-02', '-01'),INTERVAL t2.date - 1 DAY) date,
IFNULL(Sum(t1.num),0) num
FROM
t2
LEFT JOIN t1 ON t1.time LIKE CONCAT('2018-02', '%') AND DAY(t1.time) = t2.date
WHERE
DATE_ADD(CONCAT('2018-02', '-01'),INTERVAL t2.date - 1 DAY) LIKE CONCAT('2018-02', '%')
GROUP BY
t2.date