oracle 排名 年级班级,oracle sql 经典例子 班级科目排名 去重

---------------------------------------------------------------------------------------

------------------------------- 经典成绩排名 ----------------------------------------

---------------------------------------------------------------------------------------

-- drop table L_Class

create table L_Class

(

c_id varchar2(20) not null primary key,

c_name varchar2(100),

c_no varchar2(50) not null unique -- 班级编号

);

-- drop table L_Student

create table L_Student

(

s_id varchar2(20) not null primary key,

s_no varchar2(50) not null unique, -- 学号

s_name varchar2(100),

c_no varchar2(50) not null -- 班级编号

);

-- drop table L_Score;

create table L_Score

(

sc_id varchar2(20) not null primary key,

sc_score number,

sc_subject varchar2(50),

s_no varchar2(50) not null -- 学号

);

-- 外键约束

alter table L_Student

add constraint Class_Student_FK foreign key (c_no)

references L_Class (c_no);

alter table L_Score

add constraint Score_Student_FK foreign key (s_no)

references L_Student (s_no);

-----------------------

insert into L_Class values('1','1班','c1');

insert into L_Class values('2','2班','c2');

insert into L_Class values('3','3班','c3');

insert into L_Student values('1','s1','david1','c1');

insert into L_Student values('2','s2','david2','c1');

insert into L_Student values('3','s3','david3','c1');

insert into L_Student values('4','s4','tom1','c2');

insert into L_Student values('5','s5','tom2','c2');

insert into L_Student values('6','s6','tom3','c2');

insert into L_Student values('7','s7','kevin','c3');

insert into L_Student values('8','s8','jerry','c3');

insert into L_Student values('9','s9','fish','c3');

insert into L_Student values('10','s10','peter','c3');

insert into L_Score values('1','61','语文','s1');

insert into L_Score values('2','62','语文','s2');

insert into L_Score values('3','63','语文','s3');

insert into L_Score values('4','64','语文','s4');

insert into L_Score values('5','65','语文','s5');

insert into L_Score values('6','66','语文','s6');

insert into L_Score values('7','67','语文','s7');

insert into L_Score values('8','68','语文','s8');

insert into L_Score values('9','69','语文','s9');

insert into L_Score values('10','70','语文','s10');

insert into L_Score values('11','71','数学','s1');

insert into L_Score values('12','72','数学','s2');

insert into L_Score values('13','73','数学','s3');

insert into L_Score values('14','74','数学','s4');

insert into L_Score values('15','75','数学','s5');

insert into L_Score values('16','76','数学','s6');

insert into L_Score values('17','77','数学','s7');

insert into L_Score values('18','78','数学','s8');

insert into L_Score values('19','79','数学','s9');

insert into L_Score values('20','80','数学','s10');

select * from L_Student t;

select t.* from L_Class t;

select * from L_Score t;

---- (全年级) 各科 成绩排名

select *

from (

select s.s_name 姓名,

sc.sc_score 成绩,

--row_number() 行号，没有并列第一

--dense_rank() 有并列第一，接下来就是第二，

--rank() 有并列第一，接下来就是第三

dense_rank() over(partition by c.c_no, sc.sc_subject order by sc.sc_score desc) 排名,

sc.sc_subject 科目,

c.c_name 班级

from L_Class c, L_Student s, L_Score sc

where c.c_no = s.c_no

and s.s_no = sc.s_no

--and c.c_name = '3班' --查询3班各科成绩排名

) tmp

where tmp.排名 <= 3 -- 前 3 名

--对结果集按照 班级、科目 排序

order by tmp.班级 desc, tmp.科目 desc, tmp.成绩 desc;

-------------------------------------------------------------------------------------

---------------------------- group by ... having ... ---------------------------

-------------------------------------------------------------------------------------

--- 查询成绩表中成绩 >64 的记录，成绩分组

select sc.sc_score from L_Score sc group by sc.sc_score having sc.sc_score > 75 order by sc.sc_score desc;

-------------------------------------------------------------------------------------

---------------------------- 去除重复数据 -------------------------------------

-------------------------------------------------------------------------------------

drop table L_User;

create table L_User

(

u_id varchar2(20) not null primary key,

u_name varchar2(100),

u_age number

);

insert into L_User values('1','david',20);

insert into L_User values('2','david',20);

insert into L_User values('3','kevin',23);

insert into L_User values('4','tom',25);

insert into L_User values('5','kevin',30);

insert into L_User values('6','kevin',20);

select t.*,rowid,rownum from L_User t where rownum < 5;

--- 去重( 去除名字相同的记录，保留一条 )

delete from L_User u1 where u1.rowid < (select max(u2.rowid) from L_User u2 where u1.u_name=u2.u_name);

--- 去重( 去除年龄相同的记录，保留一条 )

delete from L_User u1 where u1.rowid < (select max(u2.rowid) from L_User u2 where u1.u_age=u2.u_age);

--- 去重( 去除名字、年龄都相同的记录，保留一条 )

delete from L_User u1 where u1.rowid < (select max(u2.rowid) from L_User u2 where u1.u_name=u2.u_name and u1.u_age=u2.u_age);