Question

oracle里怎么统计某个字段出现的重复次数

像这样的表

要汇聚成这样的表：AREA_ID COUNT_100 COUNT_200 COUNT_300HZ1981_1553 0 2 2HZ1981_1552 2 0 3有没有sql可以实现的？
已经解决：
select A.AREA_ID,sum(case A.RANGE_
when '100' then to_number(A.COUNT_) else 0 end) COUNT_100,
sum(case A.RANGE_
when '200' then to_number(A.COUNT_) else 0 end) COUNT_200,
sum(case A.RANGE_
when '300' then to_number(A.COUNT_) else 0 end) COUNT_300 from GIS_GRID_STAT A group by A.AREA_ID

谢谢各位的回答

Answer 1

可用count函数来计算某个字段重复次数。

如test表中数据如下：

现在要查询name列中，各个名字重复的次数，可用如下语句：

select name,count(*) from test group by name;

查询结果：

Answer 2

select a.areaid,a.count_100,b.count_200,c.count_400
where 
(select distinct areaid, count(*) count_100 from table where range_=100) a,
(select distinct areaid, count(*) count_200 from table where range_=200) b,
(select distinct areaid, count(*) count_300 from table where range_=300) c
where a.areaid=b.areaid and a.areaid=c.areaid;

Answer 3

请看下面，当然我是用SQL SERVER来写。但是在ORACLE能通用。

  select B.ID,

  case when B.h IS null then 0 else B.h end as h,

  case when  B.t IS null then 0 else  B.t end as t,

  case when  B.g IS null then 0 else  B.g end as g from (

  

  SELECT  distinct ID,

  (select [COUNT] from 表 where ID=a.id and RA=100) as h,

  (select [COUNT] from 表 where ID=a.id and RA=200) as t,

  (select [COUNT] from 表 where ID=a.id and RA=300) as g

   from 表 A

   ) B

Answer 4

select AREA_ID,sum(NVL(decode(RANGE_,100,count_),0)) as COUNT_100,
sum(NVL(decode(RANGE_,200,count_),0)) as COUNT_200,
sum(NVL(decode(RANGE_,300,count_),0)) as COUNT_300 from test1 group by area_id

Answer 5

where条件上加个那个字段，然后用sum求和