[MySQL] 전체 날짜 출력하기 - 데이터가 없는 경우 포함

참고 : https://dyang34.tistory.com/362

■ 값이 존재하지 않는 데이터의 날짜 출력하기

# 조회쿼리

SELECT a.date_ymd FROM ( SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as date_ymd 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 ) AS a WHERE 1 = 1 AND a.date_ymd BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59' -- AND a.date_ymd LIKE '2020-01-%' -- 한달만 검색 하려고 하는경우 ORDER BY a.date_ymd ASC

# 출력결과