MySQL查詢最近一周（最近7天）資料

標籤：als   規範性   har   asc   排名   sdn   分表   狀態   stat   

參考：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 timelyfrom (    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 <7ORDER BY    date DESC;

  

 

 

 

MySQL查詢最近一周（最近7天）資料