쿼리문만 가지고 MySQL, MariaDB 날짜검색시 BETWEEN 안에 포함되는 날짜 달력처럼 전체 출력

      WHERE a.date BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY),
                                       '%Y-%m-%d 00:00:00')
                       AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:29:55')

쿼리문만 가지고 MySQL, MariaDB 날짜검색시 BETWEEN 안에 포함되는 날짜 달력처럼 전체 출력하는 방법입니다.
( generate days from date range )
테이블 없이 Inline Query, Cross Join, Union, Outer Join를 이용하였습니다.
데이터가 없는 날짜는 IFNULL 함수를 이용하여 0으로 표시가 되게 합니다.

쿼리문만 가지고 MySQL, MariaDB 날짜검색시 BETWEEN 안에 포함되는 날짜 달력처럼 전체 출력

날짜의 검색 조건은 최근 한달로 설정을 하였습니다.

      WHERE a.date BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY),
                                       '%Y-%m-%d 00:00:00')
                       AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:29:55')

 

 

 

SELECT calendar.date AS reg_date, ifnull(calc.cnt, 0) cnt
FROM (SELECT DATE_FORMAT(a.date, '%m-%d') AS Date, 0 AS cnt
      FROM (SELECT   curdate()
                   - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)) DAY
                      AS Date
            FROM (SELECT 0 AS a
                  UNION ALL
                  SELECT 1
                  UNION ALL
                  SELECT 2
                  UNION ALL
                  SELECT 3
                  UNION ALL
                  SELECT 4
                  UNION ALL
                  SELECT 5
                  UNION ALL
                  SELECT 6
                  UNION ALL
                  SELECT 7
                  UNION ALL
                  SELECT 8
                  UNION ALL
                  SELECT 9) AS a
                 CROSS JOIN (SELECT 0 AS a
                             UNION ALL
                             SELECT 1
                             UNION ALL
                             SELECT 2
                             UNION ALL
                             SELECT 3
                             UNION ALL
                             SELECT 4
                             UNION ALL
                             SELECT 5
                             UNION ALL
                             SELECT 6
                             UNION ALL
                             SELECT 7
                             UNION ALL
                             SELECT 8
                             UNION ALL
                             SELECT 9) AS b
                 CROSS JOIN (SELECT 0 AS a
                             UNION ALL
                             SELECT 1
                             UNION ALL
                             SELECT 2
                             UNION ALL
                             SELECT 3
                             UNION ALL
                             SELECT 4
                             UNION ALL
                             SELECT 5
                             UNION ALL
                             SELECT 6
                             UNION ALL
                             SELECT 7
                             UNION ALL
                             SELECT 8
                             UNION ALL
                             SELECT 9) AS c
                 CROSS JOIN (SELECT 0 AS a
                             UNION ALL
                             SELECT 1
                             UNION ALL
                             SELECT 2
                             UNION ALL
                             SELECT 3
                             UNION ALL
                             SELECT 4
                             UNION ALL
                             SELECT 5
                             UNION ALL
                             SELECT 6
                             UNION ALL
                             SELECT 7
                             UNION ALL
                             SELECT 8
                             UNION ALL
                             SELECT 9) AS d) a
      WHERE a.date BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY),
                                       '%Y-%m-%d 00:00:00')
                       AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:29:55'))
     AS calendar
     LEFT JOIN
     (SELECT DATE_FORMAT(reg_date, '%m-%d') reg_date, COUNT(1) cnt
      FROM opps_calc_cont
      WHERE reg_date BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -30 DAY),
                                         '%Y-%m-%d 00:00:00')
                         AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:29:55')
      GROUP BY DATE_FORMAT(reg_date, '%m-%d')) calc
        ON calendar.date = calc.reg_date
ORDER BY calendar.date

 

select.zip

0.00MB

2021.05.18 - [유용한 활용팁] - Javascript Jquery Flot Charts 날짜별 통계 선 차트 만들기 팁

Javascript Jquery Flot Charts 날짜별 통계 선 차트 만들기 팁