在一个表A 结构如下
recordID(记录ID) type(类型) (opertDate)操作日期'类型'字段里有两种值:'自愿'和'非自愿'现在要统计某天自愿的人数、非自愿的人数效果如下日期 自愿 非自愿
2009-01-01 10 20
2009-01-02 5 8
2009-01-03 30 50
recordID(记录ID) type(类型) (opertDate)操作日期'类型'字段里有两种值:'自愿'和'非自愿'现在要统计某天自愿的人数、非自愿的人数效果如下日期 自愿 非自愿
2009-01-01 10 20
2009-01-02 5 8
2009-01-03 30 50
sum(decode(type,'自愿',1,0)) as 自愿,
sum(decode(type,'非自愿',1,0)) as 非自愿
from a
group by 日期
sum(case type when '自愿' then 1 esle 0 end) 自愿,
sum(case type when '非自愿' then 1 esle 0 end) 非自愿
from A
group by opertdate
group by to_char(opertdate,'yyyy-mm-dd');
select opertDate as 日期, count(case type when '自愿' then 1 else null end) as 自愿 ,count(case type when '非自愿' then 1 else null end) as 非自愿 from a group by opertdate
select opertDate as 日期,
sum(decode(type,'自愿',1,0)) as 自愿,
sum(decode(type,'非自愿',1,0)) as 非自愿
from A
group by opertDate;当条件多的时候也可以用case when,如下:
select opertdate as 日期,
sum(case when trim(type)='自愿' then 1 else 0 end) as "自愿",
sum(case when trim(type)='非自愿' then 1 else 0 end) as "非自愿"
from A
group by opertdate;
(select count(type) where type = '自愿' group by opertDate ) as '自愿',
(select count(type) where type = '自愿' group by opertDate )'非自愿'
from A;
试一下看看行不行吧!