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

Posted at 2020. 2. 7. 10:22 | Posted in MySQL/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




# 출력결과




Name __

Password __

Link (Your Website)

Comment

SECRET | 비밀글로 남기기