mysql 统计今天，昨天，本周，上周，本月，近七天，近30天，上一月，本季度，上季度，本年，上年的数据

CREATE TABLE `demo` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `money` int(11) NOT NULL,
  `cdate` date NOT NULL,
  `userName` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `demo` VALUES 
('1', '10', '2019-09-16', '张三'),
('2', '10', '2019-09-16', '张三'),
('3', '10', '2019-09-17', '张三'),
('4', '10', '2019-09-18', '张三'),
('5', '10', '2019-09-12', '张三'),
('6', '10', '2019-09-11', '张三'),
('7', '10', '2019-09-19', '张三'),
('8', '10', '2019-09-20', '张三'),
('9', '10', '2019-09-05', '张三'),
('10', '10', '2019-09-21', '张三'),
('11', '10', '2019-09-19', '李四'),
('12', '10', '2019-09-19', '李四'),
('13', '10', '2019-09-11', '李四'),
('14', '10', '0219-09-20', '李四'),
('15', '10', '2019-09-20', '张三'),
('16', '10', '2019-09-21', '张三'),
('17', '10', '2019-09-20', '李四'),
('18', '100', '2019-09-22', '张三'),
('19', '10', '2019-09-22', '李四');

今日

SELECT * FROM demo WHERE DATE_FORMAT(cdate,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d')

select * from demo where to_days(cdate) = to_days(now());

昨天

select * from demo where to_days(DATE_FORMAT(now(),'%Y-%m-%d')) - to_days(cdate) <=1

本周

SELECT * FROM demo WHERE YEARWEEK(date_format(cdate,'%Y-%m-%d')) = YEARWEEK(now());

上周

SELECT * FROM demo WHERE YEARWEEK(date_format(cdate,'%Y-%m-%d')) = YEARWEEK(now())-1;

近七天

select * from demo where date_sub(curdate(), interval 7 day) <= DATE(cdate)

近三十天

select * from demo where date_sub(curdate(), interval 30 day) <= date(cdate)

本月

select * from demo where date_format(cdate, '%y%m' ) = date_format(SYSDATE(), '%y%m' )

上月

select * from demo where period_diff(date_format(SYSDATE(),'%y%m'),date_format(cdate,'%y%m')) =1

本季度

select * from demo where QUARTER(cdate) = QUARTER(now());

上季度

select * from demo where QUARTER(cdate)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

年数据

select * from demo where YEAR(cdate) = YEAR(NOW());

去年数据

select * from demo where year(cdate) = year(date_sub(now(),interval 1 year));