MYSQL 현재날짜 기준으로 한달 달력 만들기

 

 

 

 

현재날짜 기준으로 이번달 31일까지의 달력 만들기 쿼리

 

SELECT 
	week AS '주'
	, MIN(CASE WHEN WEEKDAY = '2' THEN DAY END) AS '월'
	, MIN(CASE WHEN WEEKDAY = '3' THEN DAY END) AS '화'
	, MIN(CASE WHEN WEEKDAY = '4' THEN DAY END) AS '수'
	, MIN(CASE WHEN WEEKDAY = '5' THEN DAY END) AS '목'
	, MIN(CASE WHEN WEEKDAY = '6' THEN DAY END) AS '금'
	, MIN(CASE WHEN WEEKDAY = '7' THEN DAY END) AS '토'
	, MIN(CASE WHEN WEEKDAY = '1' THEN DAY END) AS '일'
FROM (
SELECT 
	WEEKOFYEAR(CDATE) AS week
	, dayofweek(cdate) AS weekday
	, dayofmonth(cdate) AS DAY
FROM (
		SELECT 
			D1 + D2 AS CDATE
		FROM 
			(
				SELECT
					str_to_date('2020-10-01', '%Y-%m-%d')D1
			)D1
			,(
				SELECT 0 D2 UNION
				SELECT 1 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 d2 ) AS TEMP
		WHERE TEMP.CDATE <= LAST_DAY(temp.cdate)
) AS TEMP2
GROUP BY week

결과값

| 주 | 월 | 화 | 수 | 목 | 금 | 토 | 일 |
----------------------------------------
| 40 |NULL|NULL|NULL| 1  | 2  | 3  | 4  |
| 41 | 5  | 6  | 7  | 8  | 9  | 10 | 11 |
| 42 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 43 | 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 44 | 26 | 27 | 28 | 28 | 30 | 31 |NULL|