잡개발자 리토의 잡위키

이 문서는 읽기 전용입니다. 원본을 볼 수는 있지만 바꿀 수는 없습니다. 문제가 있다고 생각하면 관리자에게 문의하세요.
{{htmlmetatags>
metatag-description=(달력 쿼리, Calendar Query, 달력, Calendar, 데이터베이스, DBMS, SQL, MySQL, MariaDB, MSSQL, Oracle, Tibero, PostgreSQL)
metatag-og:description=(달력 쿼리, Calendar Query, 달력, Calendar, 데이터베이스, DBMS, SQL, MySQL, MariaDB, MSSQL, Oracle, Tibero, PostgreSQL)
}}

====== Calendar Query ======

<note tip>
최소 지원 버전은 약간의 차이가 있을 수 있습니다.
</note>

===== MySQL/MariaDB =====
<sxh sql; gutter: true;>
/*
	MySQL 5.0.1 이상/MariaDB 5.1 이상
*/
SELECT
	MIN(CASE WHEN week = 1 THEN dt END) AS sun
	, MIN(CASE WHEN week = 2 THEN dt END) AS mon
	, MIN(CASE WHEN week = 3 THEN dt END) AS tue
	, MIN(CASE WHEN week = 4 THEN dt END) AS wed
	, MIN(CASE WHEN week = 5 THEN dt END) AS thu
	, MIN(CASE WHEN week = 6 THEN dt END) AS fri
	, MIN(CASE WHEN week = 7 THEN dt END) AS sat
FROM
	(SELECT
		WEEK(dt, 0) AS grp
		, DAYOFWEEK(dt) AS week
		, DATE_FORMAT(dt, '%Y-%m-%d') AS dt
		-- , DAYOFMONTH(dt) AS dt
	FROM
		(SELECT
			DATE_ADD(a, INTERVAL b DAY) AS dt
		FROM
			(SELECT
				DATE_ADD(LAST_DAY(NOW() + INTERVAL -1 MONTH), INTERVAL 1 DAY) AS a
				-- STR_TO_DATE('20250101', '%Y%m%d') AS a
			) a
			, (SELECT
				0 AS b
				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
			) b
		) cal
	WHERE
		dt <= LAST_DAY(dt)
	) cal
GROUP BY
	grp
ORDER BY
	grp ASC
;
</sxh>

===== MSSQL =====
<sxh sql; gutter: true;>
/*
	MSSQL 2000 이상
*/
SELECT
	MIN(CASE WHEN week = 1 THEN dt END) AS sun
	, MIN(CASE WHEN week = 2 THEN dt END) AS mon
	, MIN(CASE WHEN week = 3 THEN dt END) AS tue
	, MIN(CASE WHEN week = 4 THEN dt END) AS wed
	, MIN(CASE WHEN week = 5 THEN dt END) AS thu
	, MIN(CASE WHEN week = 6 THEN dt END) AS fri
	, MIN(CASE WHEN week = 7 THEN dt END) AS sat
FROM
	(SELECT
		DATEPART(WK, dt) AS grp
		, DATEPART(DW, dt) AS week
		, CONVERT(CHAR(10), dt, 23) AS dt
		-- , DAY(dt) AS dt
	FROM
		(SELECT
			DATEADD(DD, number, CONVERT(VARCHAR(6), GETDATE(), 112) + '01') AS dt
			-- DATEADD(DD, number, '20250101') AS dt
		FROM
			master.dbo.spt_values
		WHERE
			TYPE = 'P'
			AND number <= DATEDIFF(DD
				, CONVERT(VARCHAR(6), GETDATE(), 112) + '01'
				, CONVERT(VARCHAR(8), DATEADD(DD, -1, DATEADD(MM, 1, CONVERT(VARCHAR(6), GETDATE(), 112) + '01')), 112)
				-- , '20250101'
				-- , CONVERT(VARCHAR(8), DATEADD(DD, -1, DATEADD(MM, 1, '20250101')), 112)
			)
		) cal
	) cal
GROUP BY
	grp
ORDER BY
	grp ASC
;
</sxh>

===== Oracle/Tibero =====
<sxh sql; gutter: true;>
/*
	Oracle 8i 이상/Tibero 4 이상
*/
SELECT
	MIN(DECODE(week, 1, dt)) AS sun
	, MIN(DECODE(week, 2, dt)) AS mon
	, MIN(DECODE(week, 3, dt)) AS tue
	, MIN(DECODE(week, 4, dt)) AS wed
	, MIN(DECODE(week, 5, dt)) AS thu
	, MIN(DECODE(week, 6, dt)) AS fri
	, MIN(DECODE(week, 7, dt)) AS sat
FROM
	(SELECT
		TRUNC(dt + LEVEL - 1, 'D') AS grp
		, TO_CHAR(dt + LEVEL - 1, 'D') AS week
		, TO_CHAR(dt + LEVEL - 1, 'YYYY-MM-DD') AS dt
		-- , TO_CHAR(dt + LEVEL - 1, 'FMDD') AS dt
	FROM
		(SELECT
			TRUNC(SYSDATE, 'MM') AS dt
			-- TO_DATE('20250101') AS dt
		FROM
			DUAL) cal
	CONNECT BY
		LEVEL <= (LAST_DAY(dt) - dt + 1)
	) cal
GROUP BY
	grp
ORDER BY
	grp ASC
;
</sxh>

===== PostgreSQL =====
<sxh sql; gutter: true;>
/*
	PostgreSQL 8.0 이상
*/
SELECT
	MIN(CASE WHEN week = 0 THEN dt END) AS sun
	, MIN(CASE WHEN week = 1 THEN dt END) AS mon
	, MIN(CASE WHEN week = 2 THEN dt END) AS tue
	, MIN(CASE WHEN week = 3 THEN dt END) AS wed
	, MIN(CASE WHEN week = 4 THEN dt END) AS thu
	, MIN(CASE WHEN week = 5 THEN dt END) AS fri
	, MIN(CASE WHEN week = 6 THEN dt END) AS sat
FROM
	(SELECT
		CASE
			WHEN EXTRACT(DOW FROM dt) = 0 THEN DATE_TRUNC('WEEK', dt) + INTERVAL '6 DAY'
			ELSE DATE_TRUNC('WEEK', dt) + INTERVAL '-1 DAY'
		END AS grp
		, EXTRACT(DOW FROM dt) AS week
		, TO_CHAR(dt, 'YYYY-MM-DD') AS dt
		-- , EXTRACT(DAY FROM dt) AS dt
	FROM
		(SELECT
			CAST(
				GENERATE_SERIES(
					DATE_TRUNC('MONTH', CURRENT_DATE)
					, DATE_TRUNC('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH -1 DAY'
					-- DATE_TRUNC('MONTH', CAST('20250101' AS DATE))
					-- , DATE_TRUNC('MONTH', CAST('20250101' AS DATE)) + INTERVAL '1 MONTH -1 DAY'
					, INTERVAL '1 DAY'
				) AS DATE
			) AS dt
		) cal
	) cal
GROUP BY
	grp
ORDER BY
	grp ASC
;
</sxh>

{{tag>"쿼리" "Query" "응용"}}
잡개발자 리토의 잡위키

사용자 도구

사이트 도구

문서 도구
