with tmp as (select "1502" as a from dual union all select "1503" as a from dual union all select "1402" as a from dual union all select "0701" as a from dual)select a, to_char(years - w + (weeks - 1) * 7,"yyyymmdd") as begin_week from (select a, years, weeks, --因為oracle 的周是從週日開始,星期一是2 to_char(years, "d") - 2 as w from (select a, to_date("20" || substr(a, 1, 2) || "-01-01", "yyyy-mm-dd") as years, substr(a, -2, 2) as weeks from tmp))
思路：先獲取年份和週數，然後獲取該年第一天是周幾， 然後獲取該年第一週的開始時間，然後加上（週數-1）*7，例子
with tmp as (select "1502" as a from dual union all select "1503" as a from dual union all select "1402" as a from dual union all select "0701" as a from dual)select a, to_char(years - w + (weeks - 1) * 7,"yyyymmdd") as begin_week from (select a, years, weeks, --因為oracle 的周是從週日開始,星期一是2 to_char(years, "d") - 2 as w from (select a, to_date("20" || substr(a, 1, 2) || "-01-01", "yyyy-mm-dd") as years, substr(a, -2, 2) as weeks from tmp))