MySQL查询最近一周(最近7天)数据
2024-09-01 14:48:48
参考: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;
最新文章
- 《JS语言精粹》学习笔记 函数部分の闭包
- Python Day3
- Run P4 without P4factory - A Simple Example In Tutorials.
- [转]使用 C 编写 Lua 模块
- linux第5天 socket api
- memmove 和 memcpy的区别
- 【Python】使用python的tornado配合html页面示例
- Android中 判断是平板还是手机
- 有关va_list和vsnprintf输出函数的问题
- SQL优化 总结 精简
- Java 方法重载,方法重写(覆盖),继承等细节注意
- 介绍Docker仓库
- python——矩阵的奇异值分解,对图像进行SVD
- [Swift]LeetCode654. 最大二叉树 | Maximum Binary Tree
- kafka-rest:怎么愉快的build?
- linux创建桌面快捷方式
- 更改linux终端中用户名颜色
- unity2d开发windows phone游戏按钮问题
- Cesium学习2:如何从零开始在Eclipse IDE,Java语言搭建cesium开发环境
- BZOJ3653谈笑风生——可持久化线段树+dfs序
热门文章
- shiro所需的依赖
- linux pwd指令的C实现
- xgboost原理与实战
- spark中使用的内存文件系统-Tachyon FS 简介
- 拉普拉斯矩阵(Laplacian matrix)
- 用xmmp+openfire+smack搭建简易IM实现
- python threading.current_thread().name和.getName()有什么区别
- nfs服务安装部署测试
- Flutter路由(一)
- 【Deep Learning Nanodegree Foundation笔记】第 1 课:INTRODUCTION Welcome