分组排序问题

（1）根据班级分组，对各科成绩进行排名

sql语句如下：

select  
sno,
sclass,
chinese,
english,
arith,
schinese_rank,
english_rank,
arith_rank
from (
                select 
                        sno,
                        sclass,
                        chinese,
                        english,
                        arith,
                        row_number () over (partition by sclass order by arith desc) as arith_rank,
                        rank () over (partition by sclass order by chinese desc) as schinese_rank,
                        row_number () over (partition by sclass order by english desc) as english_rank
                from score
        ) t

建表语句：

create table score (
sno int,
sclass int,
chinese int,
english int,
arith int
)

insert into score values (1,1,83,57,73);
insert into score values (2,1,58,76,90);
insert into score values (3,3,89,84,96);
insert into score values (4,2,79,86,82);
insert into score values (5,1,58,81,93);
insert into score values (6,1,56,90,59);
insert into score values (7,3,77,66,77);
insert into score values (8,2,63,56,55);
insert into score values (9,1,91,78,91);
insert into score values (10,3,95,81,50);

（2）根据班级分组，对各科成绩进行排名

根据左图的数据编写sql实现右图的结果

                          

 建表语句：

create table t_group(
id number,
name varchar2(20));

insert into t_group values(1,'/');
insert into t_group values(2,'A');
insert into t_group values(3,'B');
insert into t_group values(4,'C');
insert into t_group values(5,'/');
insert into t_group values(6,'D');
insert into t_group values(7,'E');
insert into t_group values(8,'/');
insert into t_group values(9,'F');
insert into t_group values(10,'G');
insert into t_group values(11,'H');
commit;

select a.*,
id-row_number() over(order by id) group_id
from
(select * from t_group where name!='/') a;

（3）

建表语句：

create table t_goods(

goods_type varchar2(20),

goods_name varchar2(20),

price number);

insert into t_goods values('手机','华为mate',2999);

insert into t_goods values('手机','苹果iphone',7999);

insert into t_goods values('手机','荣耀v10',2399);

insert into t_goods values('手机','小米',1500);

insert into t_goods values('水果','车厘子',79);

insert into t_goods values('水果','葡萄',18);

insert into t_goods values('水果','苹果',12);

insert into t_goods values('水果','橘子',16);

insert into t_goods values('水果','芒果',7);

insert into t_goods values('电脑','金士顿16G',499);

insert into t_goods values('电脑','金士顿8G',299);

insert into t_goods values('电脑','金士顿32G',999);

insert into t_goods values('电脑','三星硬盘',599);

insert into t_goods values('电脑','华为闪存',399);

commit;

 sql：

select t1.goods_type,
t1.goods_name,
t1.price,
case when r <=0.2 then '低档'
when r >0.2 and r <=0.85 then '中档'
when r >0.85 then '高档'
end as goods_flag
from (
                select 
                        t.*,
                        dense_rank() over(partition by goods_type order by price asc)/count(goods_type) over (partition by goods_type) as r from t_goods t
        ) t1