好记性不如烂笔头 为了方便理解,咱们首先贴上代码
创建表名:
DROP TABLE IF EXISTS `weather`;
CREATE TABLE `weather` (
`id` int(11) unsigned NOT NULL auto_increment COMMENT '自增主键',
`time` varchar(50) NOT NULL COMMENT '时间',
`weather` varchar(50) NOT NULL COMMENT '天气',
`remark` varchar(50) NOT NULL COMMENT '备注',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='天气表';
向表中添加数据:
INSERT INTO `weather` VALUES ('1', '2021-05-25', '雷阵雨', '');
INSERT INTO `weather` VALUES ('2', '2021-05-24', '多云', '');
INSERT INTO `weather` VALUES ('3', '2021-05-23', '晴天', '');
INSERT INTO `weather` VALUES ('4', '2021-05-22', '阴天', '');
INSERT INTO `weather` VALUES ('5', '2021-05-21', '晴天', '');
INSERT INTO `weather` VALUES ('6', '2021-05-20', '晴天', '');
查询此时的数据:
要求:查询近一周的天气情况。
SELECT
t1.checkTime,
t2.weather
FROM
(
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 6 DAY),'%Y-%m-%d') as checkTime
UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 5 DAY),'%Y-%m-%d') as checkTime
UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 4 DAY),'%Y-%m-%d') as checkTime
UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 3 DAY),'%Y-%m-%d') as checkTime
UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 2 DAY),'%Y-%m-%d') as checkTime
UNION
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 DAY),'%Y-%m-%d') as checkTime
UNION
SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d') as checkTime
) t1 LEFT JOIN weather t2 on t1.checkTime=t2.time
ORDER BY t1.checkTime
执行上面sql得出数据,其中可以看到,19号没有天气信息,但是19号的日期仍然存在。达到预期效果,完美~