MySQL查询最近一周(最近7天)数据
参考:https://blog.csdn.net/ouyang111222/article/details/77638826
-表结构
-
CREATE TABLE `zk_score` ( `id` varchar(255) NOT NULL COMMENT '评分表', `name` varchar(255) DEFAULT NULL COMMENT '机构名称', `totalscr` varchar(255) DEFAULT NULL COMMENT '综合得分', `unfiorm` varchar(255) DEFAULT NULL COMMENT '一致性', `complete` varchar(255) DEFAULT NULL COMMENT '完整性', `standard` varchar(255) DEFAULT NULL COMMENT '规范性', `timely` varchar(255) DEFAULT NULL COMMENT '及时性', `rank` varchar(255) DEFAULT NULL COMMENT '排名', `createtime` varchar(255) DEFAULT NULL COMMENT '更新时间', `status` varchar(255) DEFAULT NULL COMMENT '状态', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-表数据
-
-SQL
-
select a.click_date as datetime, ifnull(b.totalscr,0) as totalscr, ifnull(b.standard,0) as standard, ifnull(b.complete,0) as complete, ifnull(b.unfiorm,0) as unfiorm, ifnull(b.timely,0) as timely from ( SELECT curdate() as click_date union all SELECT date_sub(curdate(), interval 1 day) as click_date union all SELECT date_sub(curdate(), interval 2 day) as click_date union all SELECT date_sub(curdate(), interval 3 day) as click_date union all SELECT date_sub(curdate(), interval 4 day) as click_date union all SELECT date_sub(curdate(), interval 5 day) as click_date union all SELECT date_sub(curdate(), interval 6 day) as click_date ) a left join ( select date(createtime) as datetime, AVG(totalscr) totalscr, AVG(standard) standard, AVG(complete) complete, AVG(unfiorm) unfiorm, AVG(timely) timely from zk_score group by date(createtime) ) b on a.click_date = b.datetime ORDER BY a.click_date ASC;
-查询结果
-
-获取近7天另一种sql
-
SELECT @num := @num + 1, date_format( date_sub( curdate()+1, INTERVAL @num DAY ), '%Y-%m-%d' ) AS date FROM zk_monit, ( SELECT @num := 0 ) t WHERE date_sub( curdate()+1, INTERVAL @num DAY ) <= date_format( curdate()+1, '%Y-%m-%d' ) and @num <7 ORDER BY date DESC;