SELECT a.company, sum(1) zs,a.iw_start, a.iw_end FROM (select company,trunc(time, 'd') iw_start, (trunc(time, 'd')+6) iw_end,fdate from table_name order by time) a GROUP BY a.company,a.iw_start,a.iw_end ORDER BY a.iw_start,a.company周日是一周的第一天,不知道这样行不行
SELECT a.company, sum(1) zs,a.iw_start, a.iw_end FROM (select company,trunc(time, 'd') iw_start, (trunc(time, 'd')+6) iw_end,time from table_name order by time) a GROUP BY a.company,a.iw_start,a.iw_end ORDER BY a.iw_start,a.company
SELECT company, zs, min_date, max_date FROM (select b.iw_start, min(time) min_date, max(time) max_date from (select company, trunc(time, 'd') iw_start, time from table_name) b group by b.iw_start) a, (select c.company, sum(1) zs, c.iw_start from (select company, trunc(time, 'd') iw_start, time from table_name) c group by c.company, c.iw_start) d where a.iw_start(+) = d.iw_start order by d.iw_start, d.company --MIN_DATE 是某一周中,数据库数据里的最小时间; --MAX_DATE 是某一周中,数据库数据里的最大时间; -- iw_start 某一日期所在周的第一天 写了好几种了 不知道你要的是哪一个
形如1楼
select to_char(sysdate,'IW') IW from dual
--返回当前时间在今年所在周次Format Models Details:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#SQLRF51079
FROM (select company,trunc(time, 'd') iw_start, (trunc(time, 'd')+6) iw_end,fdate from table_name order by time) a
GROUP BY a.company,a.iw_start,a.iw_end
ORDER BY a.iw_start,a.company周日是一周的第一天,不知道这样行不行
FROM (select company,trunc(time, 'd') iw_start, (trunc(time, 'd')+6) iw_end,time from table_name order by time) a
GROUP BY a.company,a.iw_start,a.iw_end
ORDER BY a.iw_start,a.company
FROM (select b.iw_start, min(time) min_date, max(time) max_date
from (select company, trunc(time, 'd') iw_start, time
from table_name) b
group by b.iw_start) a,
(select c.company, sum(1) zs, c.iw_start
from (select company, trunc(time, 'd') iw_start, time
from table_name) c
group by c.company, c.iw_start) d
where a.iw_start(+) = d.iw_start
order by d.iw_start, d.company
--MIN_DATE 是某一周中,数据库数据里的最小时间;
--MAX_DATE 是某一周中,数据库数据里的最大时间;
-- iw_start 某一日期所在周的第一天
写了好几种了 不知道你要的是哪一个