使用sum case做吧..sum(case when to_char(date_col,'dd') = '01' then 1 else 0 end) date_01, sum(case when to_char(date_col,'dd') = '02' then 1 else 0 end) date_02, .... sum(case when to_char(date_col,'dd') = '31' then 1 else 0 end) date_31 from .. group by 类别.
使用Rollup函数就可以了select bj,l_date,sum(times) from (select bj,to_char(thedate,'DD') l_date,count(1) times from a where thedate>= to_date('2006/02/01','YYYY/mm/dd') and thedate< to_date('2006/03/01','YYYY/mm/dd')) group by rollup (bj,l_date) having bj is not null and l_date is not null
不好意思上面那个写错了,用这个 select bj,l_date,count(1) from (select bj,to_char(thedate,'DD') l_date from a where thedate>= to_date('2006/02/01','YYYY/mm/dd') and thedate< to_date('2006/03/01','YYYY/mm/dd') and bound1=10) group by rollup (bj,l_date) having bj is not null and l_date is not null
中间的sql语句只是为了把符合条件的资料整理好,把日期转换成只有天的字符,方便外面的函数统计 select bj,to_char(thedate,'DD') l_date from a where thedate>= to_date('2006/02/01','YYYY/mm/dd') and thedate< to_date('2006/03/01','YYYY/mm/dd') and bound1=10关键是外面这个rollup要好好体会用法 select bj,l_date, count(1) from table group by rollup (bj,l_date)后面的having语句 1)bj is not null 是为了把对于l_date的小计过滤掉 2)l_date is not null 是为了把对于bj的小计过滤掉 如果楼主需要小计的话那就把后面的having语句去掉就好了
语句复杂点倒没事,主要是效率不行,数据在万以上就很慢了.我用的是oracle 9i
sum(case when to_char(date_col,'dd') = '02' then 1 else 0 end) date_02,
....
sum(case when to_char(date_col,'dd') = '31' then 1 else 0 end) date_31
from ..
group by 类别.
from
(select bj,to_char(thedate,'DD') l_date,count(1) times
from a
where thedate>= to_date('2006/02/01','YYYY/mm/dd')
and thedate< to_date('2006/03/01','YYYY/mm/dd'))
group by rollup (bj,l_date)
having bj is not null and l_date is not null
select bj,l_date,count(1)
from
(select bj,to_char(thedate,'DD') l_date
from a
where thedate>= to_date('2006/02/01','YYYY/mm/dd')
and thedate< to_date('2006/03/01','YYYY/mm/dd')
and bound1=10)
group by rollup (bj,l_date)
having bj is not null and l_date is not null
select bj,to_char(thedate,'DD') l_date
from a
where thedate>= to_date('2006/02/01','YYYY/mm/dd')
and thedate< to_date('2006/03/01','YYYY/mm/dd')
and bound1=10关键是外面这个rollup要好好体会用法
select bj,l_date, count(1)
from table
group by rollup (bj,l_date)后面的having语句
1)bj is not null 是为了把对于l_date的小计过滤掉
2)l_date is not null 是为了把对于bj的小计过滤掉
如果楼主需要小计的话那就把后面的having语句去掉就好了