求一简单SQL

Question

字段值分组统计出现次数：要求如果字段值为空，或者前三个字母是usa的，则并入 united states 进么统计，如果前三个字母是chn的，则并入china统计，其它情况按其原值分组统计。

Answer 1

decode(nvl(字段,'usa'),'chn',china,'usa',usa)

Answer 2

select col,count(*) from 
( select decode(substr(col_name,1,3),'usa','usa','chn','china','usa') as col from tab_name)
group by col;

Answer 3

select decode(substr(nvl(col1,'unknown'),0,3),'usa','united states','chn','china',col1), count(*) from tab_name 
group by decode(substr(nvl(col1,'unknown'),0,3),'usa','united states','chn','china',col1)

Answer 4

题看漏了几句，更正一下：
select col,count(*) from 
( select decode(substr(col_name,1,3),'usa','usa','chn','china',null,'usa',col_name) as col from tab_name)
group by col;