获取当前所有周及开始时间结束时间，和对应的上周数据

年度

周次

本周开始时间

本周结束时间

上周所对应年

上周所对应周

oracle数据库，我想获取这6个字段，该怎么写，每周从周一到周日视为一周，写了很多sql到跨年的时候都处理不好，有什么帆软公式可以替代吗？或者下面的sql怎么修改能保证跨年时数据展示正确

WITH DateInfo AS (

SELECT

TO_CHAR(dt, 'YYYY') AS Year,

TO_CHAR(dt, 'IW') AS Week,

TRUNC(dt, 'IW') AS WeekStart,

TRUNC(dt, 'IW') + 6 AS WeekEnd,

TO_CHAR(TRUNC(dt, 'IW') - 7, 'YYYY') AS LastYear,

TO_CHAR(TRUNC(dt, 'IW') - 7, 'IW') AS LastWeek

FROM (

SELECT TRUNC(SYSDATE, 'YYYY') + LEVEL - 1 AS dt

FROM dual

CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE, 'YYYY')

)

SELECT DISTINCT

Year,

Week,

TO_CHAR(WeekStart, 'YYYY/MM/DD HH24:MI') AS WeekStart,

TO_CHAR(WeekEnd, 'YYYY/MM/DD HH24:MI') AS WeekEnd,

LastYear,

LastWeek

FROM DateInfo

WHERE Year = TO_CHAR(SYSDATE, 'YYYY')

ORDER BY WeekStart;

FineReport RJ6688 发布于 2025-3-18 11:33

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共2回答

最佳回答

CD20160914Lv8专家互助
发布于2025-3-18 11:36（编辑于 2025-3-18 11:37）

大哥别折腾了，逻辑太多的判断，直接做一张时间维度表，直接关联一下就行了！！

我们就自己创建了一张维度表，然后里面有对应自定义的季度开始日期，自定义周范围（自然周，非自然周）以及业务需要的一切时间计算周期字段，都放在这张维度表中去！！

用户k6280494Lv6资深互助
发布于2025-3-18 11:39（编辑于 2025-3-18 11:41）

WITH RECURSIVE DateRange AS (

SELECT

TRUNC ( SYSDATE, 'IW' ) - INTERVAL '7' DAY * LEVEL + INTERVAL '1' DAY AS week_start_date,

TRUNC ( SYSDATE, 'IW' ) - INTERVAL '7' DAY * LEVEL + INTERVAL '6' DAY AS week_end_date,

TO_CHAR ( TRUNC ( SYSDATE, 'IW' ) - INTERVAL '7' DAY * LEVEL, 'YYYY' ) AS YEAR,

TO_CHAR ( TRUNC ( SYSDATE, 'IW' ) - INTERVAL '7' DAY * LEVEL, 'IW' ) AS week_number

FROM

DUAL CONNECT BY LEVEL <= CEIL (

MONTHS_BETWEEN ( TRUNC ( ADD_MONTHS ( SYSDATE, 11 ), 'YYYY' ) - INTERVAL '1' DAY, TRUNC ( SYSDATE, 'YYYY' ) ) / 12

) * 52 +

CASE

WHEN TO_CHAR ( TRUNC ( SYSDATE, 'YYYY' ), 'DD' ) > TO_CHAR (

TRUNC (

ADD_MONTHS ( TRUNC ( SYSDATE, 'YYYY' ), - ( TO_CHAR ( TRUNC ( SYSDATE, 'YYYY' ), 'MM' ) - 1 ) ),

'IW'

'DD'

) THEN

1 ELSE 0

END

)

SELECT

d.year AS "年度",

d.week_number AS "周次",

d.week_start_date AS "本周开始时间",

d.week_end_date AS "本周结束时间",

CASE

WHEN d.week_number = 1 THEN

TO_CHAR ( ADD_MONTHS ( TRUNC ( d.week_start_date, 'YYYY' ) - INTERVAL '1' DAY, 12 ), 'YYYY' ) ELSE TO_CHAR ( d.week_start_date - INTERVAL '7' DAY, 'YYYY' )

END AS "上周所对应年",

CASE

WHEN d.week_number = 1 THEN

TO_CHAR ( ADD_MONTHS ( TRUNC ( d.week_start_date, 'YYYY' ) - INTERVAL '1' DAY, 12 ) - INTERVAL '7' DAY, 'IW' ) ELSE TO_CHAR ( d.week_start_date - INTERVAL '7' DAY, 'IW' )

END AS "上周所对应周"

FROM

DateRange d

ORDER BY

d.year,

d.week_number