我是数据库方面的小菜鸟,现在有一个表主要子段为id,date,type。具体数据举例:
id date type
1 2007-03-01 3:11:00 001
2 2007-03-01 6:20:08 001
3 2007-03-02 3:11:00 002
4 2007-03-02 6:20:08 001
5 2007-03-05 3:11:00 005
6 2007-03-05 6:20:08 001
现在想统计一下每一天类型为001的数量,sql该怎么写?
id date type
1 2007-03-01 3:11:00 001
2 2007-03-01 6:20:08 001
3 2007-03-02 3:11:00 002
4 2007-03-02 6:20:08 001
5 2007-03-05 3:11:00 005
6 2007-03-05 6:20:08 001
现在想统计一下每一天类型为001的数量,sql该怎么写?
to_char('YYYY-MM-DD',date) as date,
count(*) as 数量
from
表
where
type='001'
to_char('YYYY-MM-DD',date),
count(*)
from
表
where
type='001'
and
to_char('MM',date)='03'
count(*) as 数量
from
表
where
type='001' and to_char('YYYY-MM-DD',date)>='2007-03-01' and to_char('YYYY-MM-DD',date)<='2007-03-31'
to_char('YYYY-MM-DD',date),
count(*)
from
表
where
type='001'
and
to_char('MM',date)='03'
group by date
就能统计了
比如 group by date 就要有select date 什么的
type=001的
日期 数量
2007-3-1 2
类型可以不显示
to_char('YYYY-MM-DD',date),
count(*)
from
表
where
type='001' and
to_char('MM',date)='03'
group by date;
select count(*) from table_name where type='001' and round(date)=date'2007-03-01 '这个应该没问题的恩,这个是没有错误的,不过我现在还想把所有3月份每一天的数量统计出来,sql该怎么写啊
to_date(date,'YYYY-MM-DD'),
count(*)
from
表
where
type='001' and
to_char(to_date(date,'YYYY-MM-DD'),'MM')='03'
group by date;
select
round(compl_date),count(*)
from sp.inte_order
where job_spec_id = '000061'
and round(compl_date) > date '2007-03-01' and round(compl_date) < date '2007-04-01'
group by round(compl_date);
---------- ----------- ----------
1 2007-3-1 3: 1
2 2007-3-1 6: 1
3 2007-3-2 3: 2
4 2007-3-2 6: 1
5 2007-3-5 3: 5
6 2007-3-5 6: 16 rows selectedSQL>
SQL> select trunc(ddate, 'dd') ddate, count(*) from temp
2 where type = '001'
3 group by trunc(ddate, 'dd');DDATE COUNT(*)
----------- ----------
2007-3-1 2
2007-3-2 1
2007-3-5 1SQL>
2 where type = '001'
3 group by trunc(ddate, 'mm');DDATE COUNT(*)
----------- ----------
2007-3-1 4SQL>