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;

-

-

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;

-

-

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;

---