Oracle:指定时间范围内的周分组输出.

今日QQ群有人询问 “

 大家好，我最近接到一项任务，在给定的数据库中添加 周 字段，一致为2000-01-01~2020-12-31的日期字段
   已知
   大家能提点意见吗？谢谢

”

结果类似下图，本着为以后不会忘记这个问题的想法，记录一下SQL.PS: 周六开始为1周起点，周五为周末。

/* Formatted on 11/23/2011 2:35:33 PM (QP5 v5.163.1008.3004) */
WITH src
     AS (SELECT TRUNC (DATE '2000-1-1') fromdate,
                TRUNC (DATE '2001-2-1') todate
           FROM DUAL),
     dataset
     AS (    SELECT LEVEL AS seq
               FROM DUAL
         CONNECT BY LEVEL < (SELECT todate - fromdate FROM src)),
     datawithindices
     AS (SELECT src.*,
                dataset.*,
                CASE TO_CHAR (fromdate + seq - 1, 'DY')
                   WHEN 'SAT' THEN 1
                   WHEN 'SUN' THEN 2
                   WHEN 'MON' THEN 3
                   WHEN 'TUE' THEN 4
                   WHEN 'WED' THEN 5
                   WHEN 'THU' THEN 6
                   WHEN 'FRI' THEN 7
                END
                   indice
           FROM src, dataset),
     filt
     AS (SELECT seq, fromdate + seq - 1 nowaday, indice FROM datawithindices),
     alldata
     AS (SELECT filt.*, CEIL (indice / 7) + FLOOR (seq / 7) weekno FROM filt)
SELECT 'WEEK '||weekno week_seq, begindate, enddate
  FROM (SELECT weekno,
               FIRST_VALUE (nowaday)
                  OVER (PARTITION BY weekno ORDER BY weekno)
                  begindate,
               LAST_VALUE (nowaday)
                  OVER (PARTITION BY weekno ORDER BY weekno)
                  enddate,
               ROW_NUMBER () OVER (PARTITION BY weekno ORDER BY weekno) rn
          FROM alldata)
 WHERE rn = 1

编辑器加载中...