[MYSQL] 달력(일 / 요일 테이블) 만들기
-- 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 '2022' AS y) AS d
ORDER BY dt ASC
),
-- 요일 붙이기
Calendar2 AS (
SELECT
dt AS NumDate,
DAYNAME(dt) AS EngDate
FROM Calendar
)
SELECT
Numdate
FROM Calendar2
WHERE Numdate LIKE '2022%'
ORDER BY Numdate ASC