自我感觉常用的SQL语句

今天：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(时间字段);

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

select * from 表名 where 时间字段 between date_sub(now(),interval 6 month) and now();

查询当前这周的数据：

select * from 表名 where yearweek(date_format(时间字段,'%Y-%m-%d')) = yearweek(now());

查询上周的数据：

select * from 表名 where yearweek(date_format(时间字段,'%Y-%m-%d')) = yearweek(now())-1;

查询当前月份的数据：

select * from 表名 where date_format(时间字段,'%Y-%m')=date_format(now(),'%Y-%m');

查询上个月的数据：

select * from 表名 where date_format(时间字段,'%Y-%m')=date_format(date_sub(curdate(), interval 1 month),'%Y-%m');

按年汇总，统计：

select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y');

按月汇总，统计： 

select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y-%m');

按季度汇总，统计： 

select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3)); 

select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));

按小时： 

select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by date_format(col, '%Y-%m-%d %H ');

查询 本年度的数据:

SELECT * FROM mytable WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())

查询数据附带季度数:

SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM mytable;

查询 本季度的数据:

SELECT * FROM mytable WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());

本月统计:

select * from mytable where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())

本周统计:

select * from mytable where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())

N天内记录:

WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N