统计的一类问题
表入下:
------------------
序号(id) 班级(class) 学号(sno) 获奖类别(type)
1 1 1001 a
2 1 1003 b
3 1 1009 d
4 1 1001 e
5 2 2001 a
6 2 2010 e
7 1 1007 a
统计每个班级获奖的五个类别及其所属班级获奖的次数
比如:
class type counts countstype
班级1 a 2 5
班级1 b 1 5
班级1 d 1 5我是这么用的
select *(
select a.*,
row_number() over(PARTITION by classs order by counts desc) rum
(select DISTINCT class as classs
count(type) over(partition by class,type) counts,
count(class) over(partition by class) countstype
from table1)a)
where rum<=5
不过好像不准确,求教
表入下:
------------------
序号(id) 班级(class) 学号(sno) 获奖类别(type)
1 1 1001 a
2 1 1003 b
3 1 1009 d
4 1 1001 e
5 2 2001 a
6 2 2010 e
7 1 1007 a
统计每个班级获奖的五个类别及其所属班级获奖的次数
比如:
class type counts countstype
班级1 a 2 5
班级1 b 1 5
班级1 d 1 5我是这么用的
select *(
select a.*,
row_number() over(PARTITION by classs order by counts desc) rum
(select DISTINCT class as classs
count(type) over(partition by class,type) counts,
count(class) over(partition by class) countstype
from table1)a)
where rum<=5
不过好像不准确,求教
select * from(
select class,
type,
count(1)counts,
count(distinct type)countstype,
row_number()over(partition by class order by count(1) desc)rum
from table1
group by class,type)
where rum<=5
(select class,type,counts,sum(counts) over(partition by class) countstype,
row_number() over(partition by class order by class,counts desc) rum
from (select class,type,count(type) counts from table group by class,type))
where rum<=5
select 1 id,1 class,1001 sno, 'a' type from dual
union all
select 2 id,1 class,1003 sno, 'b' type from dual
union all
select 3 id,1 class,1009 sno, 'd' type from dual
union all
select 4 id,1 class,1001 sno, 'e' type from dual
union all
select 5 id,2 class,2001 sno, 'a' type from dual
union all
select 6 id,2 class,2010 sno, 'e' type from dual
union all
select 7 id,1 class,1007 sno, 'a' type from dual
)select distinct '班级' || class,type,count(distinct class) over(partition by class,type), count(type) over(partition by class) countstype from temp
select * from(
select class,
type,
count(1)counts,
sum(count(1))over(partition by class)countstype,
row_number()over(partition by class order by count(1) desc)rum
from table1
group by class,type)
where rum<=5
不知是否有理解上的错误