[SQL] MySQL 로 달력(일 / 요일 테이블) 만들기

0. 목표

 - MySQL 로 달력 만들기

1. 실습

-- 2000-01-01 부터 + 9999 일까지 달력 만들기
WITH Calendar AS (
	SELECT CONCAT(y, '0101') + INTERVAL tt*1000 + a*100 + b*10 + c DAY AS dt
	FROM
	 (SELECT 0 AS tt
		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 tt
	, (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
	 , (SELECT 0 AS b
		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
	 , (SELECT 0 AS c
		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
	 , (SELECT '2000' AS y) AS d
	ORDER BY dt ASC
),

-- 요일 붙이기
Calendar2 AS (
	SELECT 
		dt AS NumDate,
        DAYNAME(dt) AS EngDate
	FROM Calendar
)

SELECT 
	Numdate,
    EngDate
FROM Calendar2;

2. 결과

3. 참고

 - MySQL 달력 만들기