编号 区域 类型
1 二区 商用
2 二区 民用
3 三区 商用
4 三区 民用
4 三区 民用 (注释此行是重复数据) 实现下面的统计形式 SQL该怎么写(排除重复数据) 区域 商用总数 民用总数
二区 1 1
三区 1 1
1 二区 商用
2 二区 民用
3 三区 商用
4 三区 民用
4 三区 民用 (注释此行是重复数据) 实现下面的统计形式 SQL该怎么写(排除重复数据) 区域 商用总数 民用总数
二区 1 1
三区 1 1
from (
select 区域 ,类型 ,decode(类型,'商用',1,0) as 商用,decode(类型,'民用',1,0) as 民用
from tab
)
group by 区域
select 区域,sum(商用) as 商用总数,sum(民用) as 民用总数
from (
select 区域 ,类型 ,decode(类型,'商用',1,0) as 商用,decode(类型,'民用',1,0) as 民用
from (select distinct * from table )
)
group by 区域
1 二区 商用 200
2 二区 民用 100
3 三区 商用 300
4 三区 民用 100
4 三区 民用 实现下面的统计形式 SQL该怎么写(排除编号重复数据) 区域 商用总数 民用总数 费用合计
二区 1 1 300
三区 1 1 400
改成这样语句怎么写
with t as
(
select 1 as id,'二区' as name ,'商用' as type from dual
union all
select 2 as id,'二区' as name ,'民用' as type from dual
union all
select 3 as id,'三区' as name ,'商用' as type from dual
union all
select 4 as id,'三区' as name ,'民用' as type from dual
union all
select 4 as id,'三区' as name ,'民用' as type from dual
)
select name,sum(case when type='商用' then 1 else 0 end),
sum(case when type='民用' then 1 else 0 end) from
(select distinct name,type from t)
group by name;
from (select id, area, way, fee
from t
where t.rowid =
(select max(tt.rowid)
from t tt
where tt.id = t.id and tt.fee is not null))
group by area;
with t as
(
select 1 as id,'二区' as name ,'商用' as type,200 as cast from dual
union all
select 2 as id,'二区' as name ,'民用' as type,100 as cast from dual
union all
select 3 as id,'三区' as name ,'商用' as type,300 as cast from dual
union all
select 4 as id,'三区' as name ,'民用' as type,100 as cast from dual
union all
select 4 as id,'三区' as name ,'民用' as type,0 as cast from dual
)select name,sum(case when type='商用' then 1 else 0 end),
sum(case when type='民用' then 1 else 0 end),sum(cast) from
(select * from t a where not exists(select 1 from t b where a.id=b.id and a.cast<b.cast )) t
group by name;
from (
select 区域 ,类型 ,decode(类型,'商用',1,0) as 商用,decode(类型,'民用',1,0) as 民用
from (select distinct * from table )
)
group by 区域
(
select 1 as id,'二区' as name ,'商用' as type,200 as cast from dual
union all
select 2 as id,'二区' as name ,'民用' as type,100 as cast from dual
union all
select 3 as id,'三区' as name ,'商用' as type,300 as cast from dual
union all
select 4 as id,'三区' as name ,'民用' as type,100 as cast from dual
union all
select 4 as id,'三区' as name ,'民用' as type,0 as cast from dual
)select name,sum(case when type='商用' then 1 else 0 end) 商用总数,
sum(case when type='民用' then 1 else 0 end) 民用总数,sum(cast) 费用合计 from
(select * from t a where not exists(select 1 from t b where a.id=b.id and a.cast<b.cast )) t
group by name;
name,
count(distinct case when type='商用' then 1 end ) as 商用总数,
count(distinct case when type='民用' then 1 end ) as 民用总数
from table_name
group by name;