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

最新推荐文章于 2023-06-01 11:21:55 发布

转载最新推荐文章于 2023-06-01 11:21:55 发布 · 121 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/652817

文章标签：

#数据库

本文提供了一系列MySQL查询语句，用于按不同时间段（如当天、本周、本月等）筛选数据，并展示了如何统计特定时间范围内的记录数量。此外还介绍了多表关联、函数查询及嵌套查询等高级技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

今天

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