MYSQL로 달력 만들기

SELECT    woy                                                   AS '주차'
        , MAX(CASE WHEN `wk` = '1' THEN `dom_f` END)            AS '일'
        , MAX(CASE WHEN `wk` = '2' THEN `dom_f` END)            AS '월'
        , MAX(CASE WHEN `wk` = '3' THEN `dom_f` END)            AS '화'
        , MAX(CASE WHEN `wk` = '4' THEN `dom_f` END)            AS '수'
        , MAX(CASE WHEN `wk` = '5' THEN `dom_f` END)            AS '목'
        , MAX(CASE WHEN `wk` = '6' THEN `dom_f` END)            AS '금'
        , MAX(CASE WHEN `wk` = '7' THEN `dom_f` END)            AS '토'
FROM
	(
	    SELECT *
	    FROM
	    (
	        SELECT   `date_string`
	                , DAYOFMONTH(`date_string`)             AS dom           -- day of month
	                , DAYOFWEEK(`date_string`)              AS wk          	 -- day of week( 1 - sunday, 2 - monday ... )
	                , WEEK(`date_string`)             AS woy           		   -- week of year
	                , LPAD(DAYOFMONTH(`date_string`),2,'0') AS dom_f         -- left paded day of month string( 1 -> 01 )
	        FROM
	        (
	            SELECT      CONCAT('2023-1','-',n) AS date_string        
	            FROM
	            (
	                SELECT '1' AS n        
	                UNION SELECT '2'  UNION SELECT '3'  UNION SELECT '4'  UNION SELECT '5'
	                UNION SELECT '6'  UNION SELECT '7'  UNION SELECT '8'  UNION SELECT '9'  UNION SELECT '10'
	                UNION SELECT '11' UNION SELECT '12' UNION SELECT '13' UNION SELECT '14' UNION SELECT '15'
	                UNION SELECT '16' UNION SELECT '17' UNION SELECT '18' UNION SELECT '19' UNION SELECT '20'
	                UNION SELECT '21' UNION SELECT '22' UNION SELECT '23' UNION SELECT '24' UNION SELECT '25'
	                UNION SELECT '26' UNION SELECT '27' UNION SELECT '28' UNION SELECT '29' UNION SELECT '30'
	                UNION SELECT '31' 
	            ) AS a
	        ) AS b
	    ) AS c
	    WHERE `dom` IS NOT NULL
	 ) AS d
GROUP BY `woy`;

사용 함수 정리

함수	설명	예시	응용
DAYOFMONTH	DAY와 똑같고, 1~31일 내에서 선택한 날짜의 날을 반환합니다	DAYOFMONTH(’2023-03-03’) → 3	LPAD(DAYOFMONTH('2023-03-03'),2,'0') → 03
DAYOFWEEK	1-월요일 ~ 7-일요일 (WEEKDAY : 0 - 월요일 ~ 6 - 일요일)
WEEKOFYEAR	1년 365일, 총 53주 범위에 있는 숫자를 반환, 일요일이 시작일	WEEKOFYEAR(’2023-03-03’) →9
WEEK	1년 365일, 총 53주 범위에 있는 숫자를 반환, 월요일이 시작일
LPAD	왼쪽으로 특정 문자 채우기 LAPD(초기값, 자릿수,’문자’)	LPAD((123,4,’0’) → 01234

---

• 참조
  https://github.com/yongbin/parameterize-calendar-mysql