tb你的那张表…… select mm, nvl(quantity,0) from(select rownum mm from dual connect by rownum<=12)t1, (select to_char(time,'mm') mm,count(name) quantity from tb where to_char(time,'YYYY')='2010' group by to_char(time,'mm'))t2 where t1.mm=t2.mm(+) order by t1.mm
1. select to_char(time,'yyyy-mm') mm,count(name) quantity from tb where to_char(time,'YYYY')='2010' group by to_char(time,'mm') order by to_char(time,'mm') 2. "select rownum mm from dual connect by rownum<=12" 这句算画蛇添足吗?
group by你的时间,是组查询
select to_char(time,'yyyymm'),count(name) from your_table where to_char(time,'YYYY')='2010' group by to_char(time,'yyyymm') order by to_char(time,'yyyymm')
我建了一个简单的表,然后再里面插入了几条数据如下: create table s(name varchar2(10),time date); insert into s values('tt',to_date ( '2010-02-02' , 'YYYY-MM-DD' ) );insert into s values('tt',to_date ( '2010-01-02' , 'YYYY-MM-DD' ) ); insert into s values('by',to_date ( '2010-03-02' , 'YYYY-MM-DD' ) ); insert into s values('gy',to_date ( '2010-04-02' , 'YYYY-MM-DD' ) ); insert into s values('ey',to_date ( '2010-05-02' , 'YYYY-MM-DD' ) );insert into s values('tt',to_date ( '2010-01-02' , 'YYYY-MM-DD' ) ); insert into s values('by',to_date ( '2010-03-02' , 'YYYY-MM-DD' ) ); insert into s values('gy',to_date ( '2010-04-02' , 'YYYY-MM-DD' ) ); insert into s values('ey',to_date ( '2010-05-02' , 'YYYY-MM-DD' ) ); 然后执行的查询语句是: select to_char(time, 'mm'), name, count(1) from s where to_char(time, 'yyyy') = 2010 group by to_char(time, 'mm'), name;结果就出来了,希望对你有帮助。
select to_char(time,'yyyymm'),count(distinct name) from tabl1 where to_char(time,'yyyy')='2010' group by to_char(time,'yyyymm')
select to_char(time,'yyyy-mm') mm,count(name) quantity from tb where to_char(time,'YYYY')='2010' group by to_char(time,'mm') order by to_char(time,'mm')
select to_char(time,'yyyy-mm')as mm,count(name) from tb_name where to_char(time,'YYYY')='2010' group by to_char(time,'mm') order by to_char(time,'mm') -- --或者 select to_char(time,'yyyy-mm')as mm,count(name) from tb_name where to_char(time,'YYYY')='2010' group by to_char(time,'yyyy-mm') order by to_char(time,'yyyy-mm')
支持 LuiseRADL 的写法!
select to_char(time,'yyyy-mm')as mm,count(name) from tb_name where time > to_date('2009','yyyy') and time < to_date('2011','yyyy') group by to_char(time,'yyyy-mm') order by to_char(time,'yyyy-mm')时间尽量不要用to_char
select mm,
nvl(quantity,0)
from(select rownum mm from dual connect by rownum<=12)t1,
(select to_char(time,'mm') mm,count(name) quantity from tb
where to_char(time,'YYYY')='2010'
group by to_char(time,'mm'))t2
where t1.mm=t2.mm(+)
order by t1.mm
select to_char(time,'yyyy-mm') mm,count(name) quantity from tb
where to_char(time,'YYYY')='2010'
group by to_char(time,'mm')
order by to_char(time,'mm') 2.
"select rownum mm from dual connect by rownum<=12" 这句算画蛇添足吗?
select to_char(time,'yyyymm'),count(name) from your_table
where to_char(time,'YYYY')='2010'
group by to_char(time,'yyyymm')
order by to_char(time,'yyyymm')
create table s(name varchar2(10),time date);
insert into s values('tt',to_date ( '2010-02-02' , 'YYYY-MM-DD' ) );insert into s values('tt',to_date ( '2010-01-02' , 'YYYY-MM-DD' ) );
insert into s values('by',to_date ( '2010-03-02' , 'YYYY-MM-DD' ) );
insert into s values('gy',to_date ( '2010-04-02' , 'YYYY-MM-DD' ) );
insert into s values('ey',to_date ( '2010-05-02' , 'YYYY-MM-DD' ) );insert into s values('tt',to_date ( '2010-01-02' , 'YYYY-MM-DD' ) );
insert into s values('by',to_date ( '2010-03-02' , 'YYYY-MM-DD' ) );
insert into s values('gy',to_date ( '2010-04-02' , 'YYYY-MM-DD' ) );
insert into s values('ey',to_date ( '2010-05-02' , 'YYYY-MM-DD' ) );
然后执行的查询语句是:
select to_char(time, 'mm'), name, count(1)
from s
where to_char(time, 'yyyy') = 2010
group by to_char(time, 'mm'), name;结果就出来了,希望对你有帮助。
where to_char(time,'yyyy')='2010'
group by to_char(time,'yyyymm')
where to_char(time,'YYYY')='2010'
group by to_char(time,'mm')
order by to_char(time,'mm')
select to_char(time,'yyyy-mm')as mm,count(name) from tb_name
where to_char(time,'YYYY')='2010'
group by to_char(time,'mm')
order by to_char(time,'mm')
--
--或者
select to_char(time,'yyyy-mm')as mm,count(name) from tb_name
where to_char(time,'YYYY')='2010'
group by to_char(time,'yyyy-mm')
order by to_char(time,'yyyy-mm')
where time > to_date('2009','yyyy')
and time < to_date('2011','yyyy')
group by to_char(time,'yyyy-mm')
order by to_char(time,'yyyy-mm')时间尽量不要用to_char