[Sql] 월별로 데이터 검색하기

이번달

SELECT DATE_FORMAT(NOW(), '%Y-%m') as date,
IFNULL(COUNT(regTime), 0) AS cnt FROM tbl_user
where DATE_FORMAT(regTime, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m');

한달전

SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 MONTH), '%Y-%m') as date,
IFNULL(COUNT(regTime), 0) AS cnt FROM tbl_user
where DATE_FORMAT(regTime, '%Y-%m') = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 MONTH), '%Y-%m');

두달전

SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -2 MONTH), '%Y-%m') as date,
IFNULL(COUNT(regTime), 0) AS cnt FROM tbl_user
where DATE_FORMAT(regTime, '%Y-%m') = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -2 MONTH), '%Y-%m');

INTERVAL -(날짜) MONTH 괄호 안에 부분을 수정해주면 된다
ex) -3 > 3달전, -4 > 4달전 ....