求教各位一条关于统计的SQL,不能用子查询
表数据如下:
ID, 域名,位置
1,"www.aia.com","湖南"
2,"www.abc.cn","湖南"
3,"www.xxx.com","北京"
4,"www.yyy.com","北京"
5,"www.zzz.cn","北京"
6,"www.iiii.cn","北京" 求各个位置下的.cn 域名和.com 域名的个数:
期望结果:
位置, COM个数, CN个数
湖南 1 1
北京 2 2先谢谢各位了
表数据如下:
ID, 域名,位置
1,"www.aia.com","湖南"
2,"www.abc.cn","湖南"
3,"www.xxx.com","北京"
4,"www.yyy.com","北京"
5,"www.zzz.cn","北京"
6,"www.iiii.cn","北京" 求各个位置下的.cn 域名和.com 域名的个数:
期望结果:
位置, COM个数, CN个数
湖南 1 1
北京 2 2先谢谢各位了
sum(case when instr(域名,'.com') > 0 then 1 else 0 end) "com个数",
sum(case when instr(域名,'.cn') > 0 then 1 else 0 end) "cn个数"
from tb
group by 位置
(
select 1 id ,'www.aia.com' domain ,'湖南' pos from dual union all
select 2 id ,'www.abc.cn' domain ,'湖南' pos from dual union all
select 3 id ,'www.xxx.com' domain ,'北京' pos from dual union all
select 4 id ,'www.yyy.com' domain ,'北京' pos from dual union all
select 5 id ,'www.zzz.cn' domain ,'北京' pos from dual union all
select 6 id ,'www.iiii.cn' domain ,'北京' pos from dual
)
select pos 位置,
sum(case when instr(domain,'.com') > 0 then 1 else 0 end) "com个数",
sum(case when instr(domain,'.cn') > 0 then 1 else 0 end) "cn个数"
from t
group by pos/*
位置 com个数 cn个数
---- ---------- ----------
北京 2 2
湖南 1 12 rows selected.
*/
(
select 1 id ,'www.aia.com' domain ,'湖南' pos from dual union all
select 2 id ,'www.abc.cn' domain ,'湖南' pos from dual union all
select 3 id ,'www.xxx.com' domain ,'北京' pos from dual union all
select 4 id ,'www.yyy.com' domain ,'北京' pos from dual union all
select 5 id ,'www.zzz.cn' domain ,'北京' pos from dual union all
select 6 id ,'www.iiii.cn' domain ,'北京' pos from dual
)
select pos,sum(case when '.'||upper(domain)||'.' like '%.COM.%' then 1 else 0 end) "com个数",
sum(case when '.'||upper(domain)||'.' like '%.CN.%' then 1 else 0 end) "cn个数"
from tmp
group by pos
order by pos
需要指出的问题,第一是针对www.combaba.org,那么这种不加点也是要搜索出来的,所以必须处理,然后就是www.sina.com.cn这种的计数,可能要排除
REGEXP_LIKE(domain,'.com$');
所以我改成了
select 位置,
sum(case when 域名 like '%.com') then 1 else 0 end) "com个数",
sum(case when 域名 like '%.cn' then 1 else 0 end) "cn个数"
from tb
group by 位置