【查询月份自然周】

查询自然周
思路 :1.先查询出指定月份全部日期
2.根据日期查询每个日期所在周的周一的日期和周日的日期
3.合并相同的周一 周日 第一周起始设置为1号 最后一周的结束日期设置为当月最后日期
例如 20211101

WITH ALL_DATE AS (
	SELECT
		TRUNC( to_date( '20211101', 'YYYYMMDD  HH24:MI:SS' ), 'MM' ) + ROWNUM - 1 ALL_DATE 
	FROM
		dual CONNECT BY ROWNUM <= to_char( last_day( to_date( '20211101', 'YYYYMMDD  HH24:MI:SS' ) ), 'dd' ) 
	),
	ALL_DATE1 AS (
	SELECT
		ALL_DATE,
		to_char( TRUNC( TO_DATE( to_char( ALL_DATE, 'yyyy-MM-dd' ), 'yyyy-MM-dd' ), 'IW' ), 'MMdd' ) F_week,
		to_char( TRUNC( TO_DATE( to_char( ALL_DATE, 'yyyy-MM-dd' ), 'yyyy-MM-dd' ), 'IW' ) + 6, 'MMdd' ) L_week,
		TO_CHAR( ALL_DATE, 'MM' ) moth,
		SUBSTR( to_char( last_day( ALL_DATE ), 'yyyy-mm-dd' ), 9 ) last_day 
	FROM
		ALL_DATE 
	)
	SELECT DISTINCT
	CASE
			
		WHEN
			SUBSTR( F_week, 1, 2 ) = moth THEN
				F_week ELSE moth || '01' 
				END AS F_week,
		CASE
				
				WHEN SUBSTR( L_week, 1, 2 ) = moth THEN
				L_week ELSE moth || last_day 
			END AS L_week 
		FROM
			ALL_DATE1 
		ORDER BY
			F_week