select to_char(datefield,'IW') as weeknum,
decode(count(*),0,1,count(*)) as totalnum
from t_data
where datefield between '2004-01-01' and '2004-12-30 '
group by to_char(datefield,'IW')"
decode(count(*),0,1,count(*)) as totalnum
from t_data
where datefield between '2004-01-01' and '2004-12-30 '
group by to_char(datefield,'IW')"
select to_char(to_date(substr(EDI_MesCreatDate,1,19),'yyyy-mm-dd hh24:mi:ss'),'IW') as weeknum, decode(count(*),0,1,count(*)) as totalnum from t2ashipmentno_kpi where to_date(substr(EDI_MesCreatDate,1,10),'yyyy-mm-dd') between to_date('2004-01-01','yyyy-mm-dd') and to_date('2004-12-30','yyyy-mm-dd') group by to_char(to_date(substr(EDI_MesCreatDate,1,19),'yyyy-mm-dd hh24:mi:ss'),'IW')
11 1
20 29
21 40
22 64
23 29
24 33
26 26
27 30
28 33
29 134
30 66
31 22
32 108
33 29
34 32
35 48
很多周都没有出现,
13:04:42 SQL> desc t1
名称 空? 类型
----------------------------------------- -------- ---------------------------- A DATE
B NUMBER(38)13:04:44 SQL> alter session set nls_date_format='yyyymmdd';会话已更改。已用时间: 00: 00: 00.31
13:05:01 SQL> select * from t1;
20040101 5
20040201 11
20040301 9
20040401 10
20040501 20
20040601 5
20040701 11
20040801 9
20040901 70
20041001 30
20041101 50
20041201 40已选择12行。已用时间: 00: 00: 00.32
13:05:05 SQL> select
13:05:11 2 b.dt1,sum(a.b) b
13:05:11 3 from
13:05:11 4 t1 a,
13:05:11 5 (select
13:05:11 6 distinct to_char(to_date('20040101','yyyymmdd')-1+rownum,'i
w') dt1,to_date('20040101','yyyymmdd')-1+rownum dt2
13:05:11 7 from
13:05:11 8 dba_objects
13:05:11 9 where
13:05:11 10 rownum<=to_date('20041231','yyyymmdd')-to_date('20040101','
yyyymmdd')+1
13:05:11 11 ) b
13:05:11 12 where
13:05:11 13 a.a(+)=b.dt2
13:05:11 14 group by b.dt1;
01 5
02
03
04
05 11
06
07
08
09
10 9
11
12
13
14 10
15
16
17
18 20
19
20
21
22
23 5
24
25
26
27 11
28
29
30
31 9
32
33
34
35
36 70
37
38
39
40 30
41
42
43
44
45 50
46
47
48
49 40
50
51
52
53已选择53行。已用时间: 00: 00: 00.62
a.a(+)=b.dt2
又是什么意思
select b.dt1,sum(a.b) b from t1 a,(select distinct to_char(to_date('20040101','yyyymmdd')-1+rownum,'iw') dt1,to_date('20040101','yyyymmdd')-1+rownum dt2
from dba_objects where rownum<=to_date('20041231','yyyymmdd')-to_date('20040101','yyyymmdd')+1) b where a.a(+)=b.dt2 group by b.dt1;
from dba_objects where rownum<=to_date('20041231','yyyymmdd')-to_date('20040101','yyyymmdd')+1) b这句话就是生成一个连续的从起始日期到结束日期的周数的结果集。
把这个帮忙改一下
from dba_objects where rownum<=to_date('20041231','yyyymmdd')-to_date('20040101','yyyymmdd')+1) b
表或视图不存在
ADODB.Recordset 错误 '800a0cc1' 在对应所需名称或序数的集合中,未找到项目。
日期字段就是一个列表,比如从1900-01-01到2100-12-31,全部为char或者date型。我要查询的话就以这个date_list表为主表,有关日期的查询就用右连接。那么这个问题就可以解决了。select to_char(datefield,'IW') as weeknum, count(*) as totalnum from t_data,date_list where date_list.日期 between '2004-01-01' and '2004-12-30 ' and datefield(+)=date_list.日期 group by to_char(datefield,'IW')
floor((to_char(to_date('2003-06-22','yyyy-mm-dd'),'iw')+rownum-1)/53) yearno,
mod(to_char(to_date('2003-06-22','yyyy-mm-dd'),'iw')+rownum-1,53)+1 weekno
from all_objects
where rownum<=(to_date('2005-07-30','yyyy-mm-dd')-
to_date('2003-06-22','yyyy-mm-dd'))/7+1SQL817> ed
已写入文件 afiedt.buf 1 select substr('2003-06-22',1,4)+
2 floor((to_char(to_date('2003-06-22','yyyy-mm-dd'),'iw')+rownum-1)/53) yearno,
3 mod(to_char(to_date('2003-06-22','yyyy-mm-dd'),'iw')+rownum-1,53)+1 weekno
4 from all_objects
5 where rownum<=(to_date('2005-07-30','yyyy-mm-dd')-
6* to_date('2003-06-22','yyyy-mm-dd'))/7+1
SQL817> / YEARNO WEEKNO
---------- ----------
2003 26
2003 27
2003 28
2003 29
2003 30
2003 31
2003 32
2003 33
2003 34
2003 35
2003 36
2003 37
2003 38
2003 39
2003 40
2003 41
2003 42
2003 43
2003 44
2003 45
2003 46
2003 47
2003 48
2003 49
2003 50
2003 51
2003 52
2003 53
2004 1
2004 2
2004 3
2004 4
2004 5
2004 6
2004 7
2004 8
2004 9
2004 10
2004 11
2004 12
2004 13
2004 14
2004 15
2004 16
2004 17
2004 18
2004 19
2004 20
2004 21
2004 22
2004 23
2004 24
2004 25
2004 26
2004 27
2004 28
2004 29
2004 30
2004 31
2004 32
2004 33
2004 34
2004 35
2004 36
2004 37
2004 38
2004 39
2004 40
2004 41
2004 42
2004 43
2004 44
2004 45
2004 46
2004 47
2004 48
2004 49
2004 50
2004 51
2004 52
2004 53
2005 1
2005 2
2005 3
2005 4
2005 5
2005 6
2005 7
2005 8
2005 9
2005 10
2005 11
2005 12
2005 13
2005 14
2005 15
2005 16 YEARNO WEEKNO
---------- ----------
2005 17
2005 18
2005 19
2005 20
2005 21
2005 22
2005 23
2005 24
2005 25
2005 26
2005 27
2005 28
2005 29已选择110行。SQL817>
周数却是从2开始,不知为什么