oracle sql 经典例子 班级科目排名 去重
来源:互联网 发布:免费域名注册流程 编辑:程序博客网 时间:2024/06/11 10:27
- ---------------------------------------------------------------------------------------
- ------------------------------- 经典成绩排名 ----------------------------------------
- ---------------------------------------------------------------------------------------
- -- 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);
0 0
- oracle sql 经典例子 班级科目排名 去重
- oracle sql 经典例子 班级科目排名 去重
- Oracle sql 去重
- 一道经典的oracle去重sql语句
- ORACLE经典去重问题
- oracle去除重复记录 去重 sql去重 记录
- 班级排名
- Oracle之数据去重方法 -SQL
- 问题 A: 班级排名
- 算法提高 班级排名
- oracle去重整理
- Oracle去重
- oracle数据库去重
- oracle 去重
- 去重oracle
- Oracle中的去重
- oracle去重
- oracle去重手段
- @WebServlet
- Windows下搭建PHP开发环境
- jquery ajax参数用法记录
- 中国软件开发者薪资调查报告
- POI与JXL操作Excel的区别
- oracle sql 经典例子 班级科目排名 去重
- 手游公司日记(6)
- 朴素贝叶斯(NaiveBayes)算法总结
- Linux系统内存管理之伙伴系统分析
- JQuery实例1:用户名校验
- ContextMenuFragment使用分析menu dialog
- POJ 2079 求最大三角形面积 (凸包+旋转卡壳)
- gb2312tobig5 delphi简繁转换
- Android开发者指南-传感器-概述