--------------------------------------------------------------------------------------- ------------------------------- 经典成绩排名 ---------------------------------------- --------------------------------------------------------------------------------------- -- 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);
相关推荐
oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具...
oracle字段去重 以某一个字段去重 oracle字段去重 以某一个字段去重
oracle sql案例的分享ppt,具体调优和改写sql的例子
ORACLE SQL性能优化系列 ORACLE SQL性能优化系列 ORACLE SQL性能优化系列
Oracle Sql语句转换成Mysql Sql语句java 源码,非常简单,只要给定源oracle sql语句地址,和生成目标文件地址运行即可。
Oracle SQL:经典查询练手四篇Oracle SQL:经典查询练手四篇Oracle SQL:经典查询练手四篇
主要讲述oracle sql 的开发以及优化,对低效率的sql的优化方法和诊断技巧
精通Oracle SQL【第2版】ORACLE SQL高级编程【第二版】学习笔记
Welcome to “Beginning SQL for Oracle Database 18C”! Thanks for picking up this book. It’s safe to say you’re interested in learning about Oracle database, as that’s what this book is about. Why ...
oracle sql 经典查询范例 有十几个例子 是学习sql的好例子
传入一个字符串和该字符串的分割字符,返回去重后的字符串,可以直接在plsql中运行,简单的函数运用,能处理oracle中。资源仅供参考
Oracle Sql基础(beginning oracle sql中文版)
Oracle SQL 11G2 官方文档,包含4份,分别是PLSQL语言参考、PLSQL程序包与类型、Oracle SQL参考、SQL快速参考
Oracle SQL 内置函数大全 SQL中的单记录函数 给出整数,返回对应的字符 连接两个字符串 增加或减去月份 用于对查询到的结果进行排序输出
sql语句:按照某一个字段进行去重后获取全部字段。
OracleSQL的优化.pdf
OracleSQL必备参考OracleSQL必备参考OracleSQL必备参考OracleSQL必备参考
资深开发DBA对Oracle SQL编写规范的总结
Oracle SQL Developer is a relatively new product included in the range of Oracle products. It was frst introduced to the world in September 2005, by its code name Project Raptor. Raptor is a name ...