select Trunc(sysdate,'mm') , count(flag) from record group by Trunc(sysdate,'mm')
select add_months(trunc(sysdate,'yyyy'),rownum-1) mon, ( select count(flag) cnum from record where flag='0' and trunc(coldate,'mm')=add_months(trunc(sysdate,'yyyy'),rownum-1) ) data from all_source where rownum<=12;
select add_months(trunc(sysdate,'yyyy'),rownum-1) mon, ( select count(flag) cnum from record where flag='0' and trunc(coldate,'mm')=add_months(trunc(sysdate,'yyyy'),rownum-1) ) data0, ( select count(flag) cnum from record where flag='1' and trunc(coldate,'mm')=add_months(trunc(sysdate,'yyyy'),rownum-1) ) data1 from all_source where rownum<=12;
select count(flag) cnum from record where flag='0' and trunc(coldate,'mm')=add_months(trunc(sysdate,'yyyy'),rownum-1) 表record的rownum ) data from all_source where rownum<=12; 表all_source的rownum这两个rownum所代表的含义不一样,不是指的同一个rownum
to:CodeMagic(写错了吧) 我是初学者,听不太明白,请问我该怎么改呢?谢谢
select a,sum(num) from ( select trunc(dat,'mm') a,count(*) num from record where flag=0 group by trunc (dat,'mm') union select add_months(trunc(sysdate,'yyyy'),rownum-1) a,0 num from dba_tables where rownum<13 ) group by a
select a,sum(num) from ( select trunc(dat,'mm') a,count(*) num from record where flag=0 group by trunc(dat,'mm') union select add_months(trunc(sysdate,'yyyy'),rownum-1) a,0 num from dba_tables where rownum<13 ) group by a
你这统计只能统计出在表里面当月有数据的部分,而没数据的月却统计不出
group by Trunc(sysdate,'mm')
(
select count(flag) cnum from record where flag='0' and
trunc(coldate,'mm')=add_months(trunc(sysdate,'yyyy'),rownum-1)
) data
from all_source where rownum<=12;
请问我想同时统计出flag=1的数目,又该怎么写呢,谢谢
(
select count(flag) cnum from record where flag='0' and
trunc(coldate,'mm')=add_months(trunc(sysdate,'yyyy'),rownum-1)
) data0,
(
select count(flag) cnum from record where flag='1' and
trunc(coldate,'mm')=add_months(trunc(sysdate,'yyyy'),rownum-1)
) data1
from all_source where rownum<=12;
兄弟,用您这语句统计出的结果怎么全都是零啊,与表中实际结果不同
MON DATA0 DATA1
---------- ---------- ----------
01-1月 -04 0 0
01-2月 -04 0 0
01-3月 -04 0 0
01-4月 -04 0 0
01-5月 -04 0 0
01-6月 -04 0 0
01-7月 -04 0 0
01-8月 -04 0 0
01-9月 -04 0 0
01-10月-04 0 0
01-11月-04 0 0MON DATA0 DATA1
---------- ---------- ----------
01-12月-04 0 0已选择12行。
trunc(coldate,'mm')=add_months(trunc(sysdate,'yyyy'),rownum-1) 表record的rownum
) data from all_source where rownum<=12; 表all_source的rownum这两个rownum所代表的含义不一样,不是指的同一个rownum
我是初学者,听不太明白,请问我该怎么改呢?谢谢
(
select trunc(dat,'mm') a,count(*) num from record where flag=0 group by trunc
(dat,'mm') union select add_months(trunc(sysdate,'yyyy'),rownum-1) a,0 num from dba_tables where
rownum<13
)
group by a
(
select trunc(dat,'mm') a,count(*) num from record where flag=0 group by trunc(dat,'mm')
union
select add_months(trunc(sysdate,'yyyy'),rownum-1) a,0 num from dba_tables where rownum<13
)
group by a