最近想用Echart做数据统计的图形显示，数据来源是MySQL数据库，自然需要根据不同的搜索条件筛选出表中的数据，用比较多的就是时间的参数吧！

常用的mysql时间的条件进行检索的SQL语句：

数据表名:tableName 字段名:myDate 

最近7天的,
SELECT * FROM tableName where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(myDate);

最近一个月的
SELECT * FROM tableName where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(myDate);

最近1年的
SELECT * FROM tableName where DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= date(myDate);

本周的 (是从周日开始)
SELECT * FROM tableName where YEARWEEK(DATE_FORMAT(myDate,'%Y-%m-%d')) = YEARWEEK(now());

上周的
SELECT * FROM tableName where YEARWEEK(DATE_FORMAT(myDate,'%Y-%m-%d')) = YEARWEEK(now())-1;

本月的
SELECT * FROM tableName where date_format(myDate,'%Y-%m')=date_format(now(),'%Y-%m')

上个月的
SELECT * FROM tableName where date_format(myDate,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');

距离现在6个月的数据
SELECT * FROM tableName where myDate between date_sub(now(),interval 6 month) and now();

今年的数据，按月分组
SELECT month(fly_timeStart) as month,count(*) FROM flightinfo.tab_flight_info where year(fly_timeStart)=2019 group by month(fly_timeStart);

获取当月第一天
select DATE_ADD(CURDATE(),INTERVAL-DAY(CURDATE())+1 day);

获取上月最后一天
select DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())+0 day);
我想做的是统计最近一年，不包括当月的数据，按月统计；以及当月的数据，按天统计。

开始的语句是这样的，但是这是按当前日期往前推，我想要从当月第一天往前推，即不包括当月的数据。

最近1年的数据，按月分组
SELECT plane_type, year(fly_timeStart) as year,month(fly_timeStart) as month,count(*) as count 
FROM tab_flight_info where DATE_SUB(curdate(), INTERVAL 1 YEAR) <= date(fly_timeStart) AND plane_type='机型1' 
group by year(fly_timeStart),month(fly_timeStart);
于是改成如下语句，curdate() 改成 DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())+0 day：

最近1年的数据，按月分组
SELECT plane_type, year(fly_timeStart) as year,month(fly_timeStart) as month,count(*) as count FROM tab_flight_info where
 DATE_SUB(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())+0 day), INTERVAL 1 YEAR) <= date(fly_timeStart) AND plane_type='机型1' 
group by year(fly_timeStart),month(fly_timeStart);
然后是按月统计：

最近一个月的数据，按天分组
SELECT month(fly_timeStart),day(fly_timeStart),count(*) FROM flightinfo.tab_flight_info where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(fly_timeStart) 
group by month(fly_timeStart),day(fly_timeStart);

获取当月数据
SELECT * from tab_flight_info where DATE_FORMAT(fly_timeStart,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');
获取当月数据
SELECT month(fly_timeStart) as month,day(fly_timeStart) as day,count(*) as count from tab_flight_info 
where DATE_FORMAT(fly_timeStart,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m') AND plane_type='机型1' group by day(fly_timeStart);
 

转载于:https://www.cnblogs.com/AndrewYin/p/11265706.html