************如何按周汇总时当此周无记录默认为零？**************

Question

select to_char(ACT_InsertDate,'IW') as weeknum,   NVL(count(act_shpmtno),0)   as totalnum from T2AShipmentNo_KPI where act_shpmtno is not null and t2ashipmentno_kpi.act_insertdate between to_date('2004-04-01','yyyy-mm-dd') and to_date('2004-06-06','yyyy-mm-dd') group by to_char(ACT_InsertDate,'IW') 
--用NVL

Answer 1

create view ABC as select to_char(ACT_InsertDate,'IW') as weeknum, count(act_shpmtno) as totalnum from T2AShipmentNo_KPI where act_shpmtno is not null and t2ashipmentno_kpi.act_insertdate between to_date('2004-04-01','yyyy-mm-dd') and to_date('2004-06-06','yyyy-mm-dd') group by to_char(ACT_InsertDate,'IW')create view XYZ as select to_char(to_date('2004-04-01','yyyy-mm-dd'),'iw')+rownum-1 weeknum from all_tables where rownum<to_char(to_date('2004-06-06','yyyy-mm-dd'),'iw')-to_char(to_date('2004-04-01','yyyy-mm-dd'),'iw')+2;
--下面是查询select * from xyz,abc where xyz.weeknum=abc.weeknum(+);

Answer 2

上面建视图，是为了书写清楚，楼主可根据自己的需要考虑。'2004-04-01'在视图xyz中表示统计的开始时间
'2004-06-06'在视图xyz中表示统计的结束时间