select sale_date,sale_area,count(*)
from tablename
where sale_date='2006-01-01' and (sale_area='aaa' or sale_area='bbb' )
group by sale_date,sale_area
union
select sale_date,'其它' as sale_area,count(*)
from tablename
where sale_date<>'2006-01-01' or (sale_area<>'aaa' and sale_area<>'bbb' )
group by sale_date
from tablename
where sale_date='2006-01-01' and (sale_area='aaa' or sale_area='bbb' )
group by sale_date,sale_area
union
select sale_date,'其它' as sale_area,count(*)
from tablename
where sale_date<>'2006-01-01' or (sale_area<>'aaa' and sale_area<>'bbb' )
group by sale_date
decode(sale_area, 'aaa', sale_area, 'bbb', sale_area, 'Others') sale_area,
count(*) counts
from sale
group by sale_date,
decode(sale_area, 'aaa', sale_area, 'bbb', sale_area, 'Others')
假如我要得到如下的结果该怎么写:
sale_date aaa bbb others
2006-01-01 1 3 2
2006-01-02 0 0 1
sum(decode(t.sale_area, 'aaa',1,0)) as aaa,
sum(decode(t.sale_area, 'bbb', 1,0)) as bbb,
sum(decode(t.sale_area, 'aaa', 0, 'bbb', 0,1)) as others
from 表 t
group by t.sale_date