文档章节

mysql 查询当天、本周,本月,上一个月的数据

B
 BraveLN
发布于 01/19 17:44
字数 1150
阅读 6
收藏 0

今天

select * from 表名 where to_days(时间字段名) = to_days(now());

昨天

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

近7天

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

近30天

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

本月

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

查询本季度数据

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

查询上季度数据

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

查询本年数据

select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

查询上年数据

select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

查询当前这周的数据

SELECT name,时间字段名FROM enterprise WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now());

查询上周的数据

SELECT name,时间字段名FROM enterprise WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now())-1;

查询上个月的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) 

select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

select * from user where pudate between  上月最后一天  and 下月第一天 

查询当前月份的数据

select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

查询距离当前现在6个月的数据

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

查询每天每周每月每年的总数

查询每天的数据:

SELECT
    COUNT(1) AS countNumber,
    DATE_FORMAT(createTime,'%Y-%m-%d') AS dateTime
FROM
    testTable
GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d')

查询每周的数据:

SELECT
    COUNT(1) AS countNumber,
    WEEK(createTime) as dateTime
FROM
    testTable
GROUP BY WEEK(createTime) 

查询每月的数据:

SELECT
    COUNT(1) AS countNumber,
    MONTH(createTime) as dateTime
FROM
    testTable
GROUP BY MONTH(createTime)

查询每年的数据:

SELECT
    COUNT(1) AS countNumber,
    YEAR(createTime) as dateTime
FROM
    testTable
GROUP BY YEAR(createTime)

其它:

DATE_ADD(createTime,INTERVAL 1 DAY) 得到指定日期后一天的日期/把1改为任意数字就可以得到后N天的日期

DATE_SUB(createTime,INTERVAL 1 DAY) 得到指定日期前一天的日期/把1改为任意数字就可以得到前N天的日期

声明变量查询:

SET @counter=0;
select @counter:=@counter+1 as id,count(*) as num FROM testTable ORDER BY num desc

去重选择后返回条数

SELECT COUNT(DISTINCT(id)) from 数据表 WHERE createdDate BETWEEN '2009-08-06 15:53:14' AND '2009-09-01 15:53:14'

查出第几周到第几周的区间数据:

SELECT 
COUNT(DISTINCT(id)) ,
WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1) AS startweek,
WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1) AS endweek
from 数据表
WHERE
 WEEK(date_format(createdDate,'%Y-%m-%d'),1)
BETWEEN
 WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1)
 AND WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1);

多表关联查询:

SELECT
COUNT(DISTINCT(account)) as fuNum, 
op.*,
p.*
from 
A o
LEFT JOIN B op
ON o.id = op.id
LEFT JOIN C p
ON op.id = p.id
WHERE
 WEEK(date_format(createdDate,'%Y-%m-%d'),1)
BETWEEN
 WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1)
 AND WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1)
AND id = 3

函数查询求和:

SELECT
COUNT(DISTINCT(op.a)) as pa, 
COUNT(p.b) AS sa,
SUM(o.c) AS ta
from 
A o
LEFT JOIN B op
ON o.id = op.id
LEFT JOIN C p
ON op.id = p.id
WHERE
 WEEK(date_format(createdDate,'%Y-%m-%d'),1)
BETWEEN
 WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1)
 AND WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1)
AND id = 3

嵌套查询:

SELECT
DISTINCT
(CASE  account
WHEN '0' THEN 'null'
ELSE (SELECT COUNT( DISTINCT(account) ) FROM  eps_order WHERE payStatus = 'paid') END) AS payNum,
COUNT(p.`order`) AS saleOrders,
SUM(o.amount) AS totalAmount
from 
A o
LEFT JOIN B op
ON o.id = op.orderID
LEFT JOIN C p
ON op.productID = p.id
WHERE
 WEEK(date_format(createdDate,'%Y-%m-%d'),1)
BETWEEN
 WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1)
 AND WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1)
AND  o.`status` != 'canceled'
AND productID = 3

多重嵌套查询:


SELECT
DISTINCT
(CASE  o.account
WHEN NULL THEN 'null'
ELSE 
(
SELECT 
COUNT( DISTINCT(o.account) ) 
from 
A o
LEFT JOIN B op
ON o.id = op.orderID
LEFT JOIN C p
ON op.productID = p.id
WHERE
 WEEK(date_format(createdDate,'%Y-%m-%d'),1)
BETWEEN
 WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1)
 AND WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1)
AND  o.`status` != 'canceled'
AND productID = 3
) 
END) AS payNum,
COUNT(p.`order`) AS saleOrders,
SUM(o.amount) AS totalAmount
from 
A o
LEFT JOIN B op
ON o.id = op.orderID
LEFT JOIN C p
ON op.productID = p.id
WHERE
 WEEK(date_format(createdDate,'%Y-%m-%d'),1)
BETWEEN
 WEEK(date_format('2018-08-05 15:53:14','%Y-%m-%d'),1)
 AND WEEK(date_format('2018-08-06 15:53:14','%Y-%m-%d'),1)
AND  o.`status` != 'canceled'
AND productID = 3

© 著作权归作者所有

共有 人打赏支持
B
粉丝 1
博文 23
码字总数 9155
作品 0
海淀
高级程序员
私信 提问
mysql 按年度、季度、月度、周、日SQL统计查询

表结构: 表中数据总条数:2926 1. 统计每天的单数 SELECT COUNT(),DATE_FORMAT(t.create_time,'%Y-%m-%d') from t_call_record t GROUP BY DATE_FORMAT(t.create_time,'%Y-%m-%d') (执行时......

kuchawyz
2018/10/25
0
0
MySQL关于根据日期查询数据的sql语句

MySQL关于根据日期查询数据的sql语句 查询往前7天的数据: select * from 数据表 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= 你要判断的时间字段名 查询往前30天的数据: select * from 数...

xiaocao13140
2018/06/06
0
0
MySQL 按时间统计数据

1.统计当天数据量 2.统计昨天数据量 3.统计n天前的数据量 4.统计本周的数据量 5.统计上周数据量 6.统计本月的数据 7.统计上个月的数据 8.统计近30天的数据 9.统计今年数据 10.统计去年...

满小茂
2016/06/16
65
0
Mysql学习总结(57)——MySQL查询当天、本周、本月、上周、本周、上月、距离当前现在6个月数据

--------------查"当天"的数据----------------- SELECT ew.orderid, ew.carid, ew.userid, ew., ew.reservetime, ew.starttime, ew.endtime, ew.canceltime, eu.nickname, ec.plateid FROM ......

u012562943
2018/04/12
0
0
Mysql查询本周,上周,本月,上月数据

查询当前这周的数据 SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now()); 查询上周的数据 SELECT name,submittime FROM enterp......

Linland
2013/03/27
0
0

没有更多内容

加载失败,请刷新页面

加载更多

12_第一个Flutter程序

使用 package 在这一步中,你将开始使用一个名为 english_words 的开源软件包,其中包含数千个最常用的英文单词以及一些实用功能。 你可以 在 pub.dartlang.org 上找到 english_words 软件包...

口十耳
11分钟前
0
0
指明方向与趋势!2019开发者技能报告出炉!!!

近日国外开发者平台 HankerRank 发布了 2019 年开发者技能调查报告( https://research.hackerrank.com/developer-skills/2019 ),该报告根据对71,281开发者的调查得出。 2018 年最受欢迎的...

阿里云官方博客
12分钟前
0
0
【Maven冷知识】Compiler插件

很多同学在pom的配置中都喜欢加上这样一段配置信息: 从配置信息上看,这是maven对Java源代码进行的编译配置,采用了Java 7 进行编译,但是为什么要加上这段配置呢?不加有没有什么影响?很多...

算法与编程之美
16分钟前
0
0
磊哥测评之数据库SaaS篇:腾讯云控制台、DMC和小程序

本文由云+社区发表 作者:腾讯云数据库 随着云计算和数据库技术的发展,数据库正在变得越来越强大。数据库的性能如处理速度、对高并发的支持在节节攀升,同时分布式、实时的数据分析、兼容主...

腾讯云加社区
17分钟前
0
0
Visual Studio系列教程:使用XAML工具创建用户界面(二)

Visual Studio是一款完备的工具和服务,可帮助您为Microsoft平台和其他平台创建各种各样的应用程序。在本系列教程中将介绍如何为图像编辑创建基本的用户界面,有任何建议或提示请在下方评论区...

ymy_666666
19分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部