推荐第一种写法
--需求,自定义一个日期开始 以7天为一个周期 那么可以如下实现
--level<=值 / NTILE(N) = 7
select DATE '2021-11-07' + level day,
NTILE(200) over(order by DATE '2021-12-01' + level) week
from dual
connect by level <= 1400;
这种是不用NTILE(N) 函数实现的
如下SQL,是以2020-11-08作为周一,然后以第一周开始,并且7天为一周,要处理跨年
SELECT W.*, W.WEEK - NVL(W2.WEEK2, 0) WEEK_NEW
FROM (SELECT C.dt, C.week, B2.M_DT, B2.M_DT + 6
FROM (SELECT B.DT, trunc((B.rn - 1) / 7) + 1 week
FROM (SELECT A.dt, rn, 7 * trunc((rn - 1) / 7) AS rn2
FROM (SELECT DATE '2020-11-08' + level - 1 AS dt,
rownum rn
FROM dual
connect by level < 900) A) B) C,
(SELECT MIN(B.DT) M_DT, trunc((B.rn - 1) / 7) + 1 week
FROM (SELECT A.dt, rn, 7 * trunc((rn - 1) / 7) AS rn2
FROM (SELECT DATE '2020-11-08' + level - 1 AS dt,
rownum rn
FROM dual
connect by level < 900) A) B
GROUP BY trunc((B.rn - 1) / 7) + 1) B2
WHERE C.week = B2.week(+)
Order by C.dt) W
LEFT JOIN (SELECT *
FROM (SELECT trunc((B.rn - 1) / 7) + 1 week,
MIN(B.DT) MIN_DT,
MAX(B.DT) MAX_DT,
(CASE
WHEN to_char(MIN(B.DT), 'yyyy') <>
to_char(MAX(B.DT), 'yyyy') THEN
trunc((B.rn - 1) / 7) + 1
WHEN MIN(B.DT) = trunc(MIN(B.DT), 'yyyy') THEN
--以trunc((B.rn - 1) / 7) + 1分组,所以要包起来-1才行
(trunc((B.rn - 1) / 7) + 1) - 1
ELSE
0
END) WEEK2
FROM (SELECT A.dt, rn, 7 * trunc((rn - 1) / 7) AS rn2
FROM (SELECT DATE '2020-11-08' + level - 1 AS dt,
rownum rn
FROM dual
connect by level < 900) A) B
GROUP BY trunc((B.rn - 1) / 7) + 1) A
WHERE A.WEEK2 <> 0) W2
ON to_char(W.M_DT, 'YYYY') = to_char(W2.MAX_DT, 'yyyy')
