开发者社区> 浅色夏墨> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: mysql 查询当天、本周,本月,上一个月的数据 今天 select * from 表名 where to_days(时间字段名) = to_days(now()); 昨天 SELECT * FROM 表名 WHERE TO_DAYS( NOW( )...
+关注继续查看

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


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL 查询数据
MySQL 查询数据
29 0
mysql数据库(9):常用查询的例子(上)
mysql数据库(9):常用查询的例子
39 0
mysql数据库(9):常用查询的例子(下)
mysql数据库(9):常用查询的例子
36 0
mysql查询当天所有数据sql语句
mysql查询当天的所有信息: select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now())这个有一些繁琐,还有简单的写法: selec...
2311 0
查看MYSQL中数据表占用的空间
由于数据太大了。所以MYSQL需要瘦身,那前提就是需要知道每个表占用的空间大小。 首先打开指定的数据库: use information_schema;   如果想看指定数据库中的数据表,可以用如下语句: select concat(round(sum(DATA_LENGTH/...
848 0
查看MySQL数据库大小
查看MySQL数据库大小 1、进去指定schema 数据库(存放了其他的数据库的信息) use information_schema 2、查询所有数据的大小 select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') ...
657 0
+关注
76
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载