如何查找重复数据
【题目】
编写一个SQL查询,查找学生表中所有重复的学生名。
建表语句
create table students (id varchar2(4),name varchar2(4));
insert into students values(1,'张三');
insert into students values(2,'李四');
insert into students values(3,'王五');
insert into students values(4,'王五');
insert into students values(5,'赵六');
方法1使用having语句
select name
from students
group by name
having count(name) > 1;
方法2使用子查询
select name
from (select name, count(name) as "计数" from students group by name) "辅助表"
where "计数" > 1;
–如何查找第N高的数据?
/【题目】
现在有“课程表”,记录了学生选修课程的名称以及成绩。
现在需要找出语文课中成绩第二高的学生成绩。如果不存在第二高成绩的学生,那么查询应返回 null。/
–建表语句
create table score(id varchar2(2),course varchar2(4),results varchar2(4));
insert into score values(1,‘语文’,90);
insert into score values(1,‘数学’,65);
insert into score values(2,‘语文’,68);
insert into score values(2,‘数学’,96);
insert into score values(3,‘数学’,55);
–方法1使用rownum伪劣
select results "语文课第二高的成绩"
from (select distinct results, rownum r
from score
where course = '语文'
order by results desc)
where r = 2;
–方法2使用子查询找出语文成绩查询最大的成绩记为a,然后再找出小于a的最大值就是课程成绩的第二高值。
select nvl(max(distinct results), null) "语文课第二高的成绩"
from score
where course = '语文'
and results <
(select max(distinct results) from score where course = '语文');