你这个不是自然周,而是你每7天人为规定的一个周 每周生成一个视图不好吧,oracle没有带参视图, 用一个存储过程来处理输入需要第几周,然后计算出 具体的日期范围来查询即可,参考: CREATE OR REPLACE PROCEDURE getweek(i_week PLS_INTEGER, o SYS_REFCURSOR) IS begin_date DATE; end_date DATE; BEGIN begin_date := trunc(SYSDATE, 'yyyy') + 7 * i_week; end_date := begin_date + 7; OPEN o FOR SELECT * FROM table1 WHERE mydate BETWEEN begin_date AND end_date; END;
create view ViewName as select extract(year from weekstart) year, '第' || to_char(weekstart, 'ww') || '周' weekno, to_char(weekstart, 'yyyy-mm-dd') || '~' || to_char(case when extract(year from weekstart + 6) > extract(year from weekstart) then add_months(trunc(weekstart, 'yy'), 12) - 1 else weekstart + 6 end, 'yyyy-mm-dd') weekrange, numSum from (select trunc(DateCol, 'ww') weekstart, sum(NumCol) numSum from TabName group by trunc(DateCol, 'ww')) order by 1, 2
每周生成一个视图不好吧,oracle没有带参视图,
用一个存储过程来处理输入需要第几周,然后计算出
具体的日期范围来查询即可,参考:
CREATE OR REPLACE PROCEDURE getweek(i_week PLS_INTEGER, o SYS_REFCURSOR) IS
begin_date DATE;
end_date DATE;
BEGIN
begin_date := trunc(SYSDATE, 'yyyy') + 7 * i_week;
end_date := begin_date + 7;
OPEN o FOR
SELECT * FROM table1 WHERE mydate BETWEEN begin_date AND end_date;
END;
select extract(year from weekstart) year,
'第' || to_char(weekstart, 'ww') || '周' weekno,
to_char(weekstart, 'yyyy-mm-dd') || '~' ||
to_char(case
when extract(year from weekstart + 6) >
extract(year from weekstart) then
add_months(trunc(weekstart, 'yy'), 12) - 1
else
weekstart + 6
end,
'yyyy-mm-dd') weekrange,
numSum
from (select trunc(DateCol, 'ww') weekstart, sum(NumCol) numSum
from TabName
group by trunc(DateCol, 'ww'))
order by 1, 2
oracle QQ群:54775466
新建欢迎积极探讨者共同探讨