MySQL查询最近一周(最近7天)数据

2018-08-13 07:38:11来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折

参考: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;

  

 

 

 


标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:java-并发编程

下一篇:IDEA 倒入maven项目下载很慢