Oracle查询相同课程数

Oracle中的查询最能提升一个人的思维,一道题目可以有不同的方法进行解决,先来看下例子,如下:

题目:查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

先看下表结构以及部分表数据,如下图:

Oracle查询相同课程数

Oracle查询相同课程数

上面这个题目很简单,因为只有一门相同课程就可以,先查询出同学’1001’的课程,再成绩表中in一下就可以了,所以我就想啊,一门简单,两门呢?全部呢?下面简单说明下我的思路。

--思路:

--1、首先将学号"1001"的同学的课程号查出;

--2、再与成绩表进行内连接,条件是课程id相同,筛选出学过学生"1001"所学的课程的学生;

--3、最后进行分组排序,将学过学生"1001"同学的课程数与学生"1001"的课程数进行比较,一门课程就不用比较了,内连接最少也是一门课程!

 

Oracle代码:

学过一门;

select distinct t.stuid,stuname from tblStudent t join tblScore s on s.StuId = t.StuId

where t.StuId <> '1001' and s.CourseId in (select CourseId from tblScore where StuId = '1001');

--或

select stuid,stuname from tblStudent where StuId in (

select stuid from(

select b.* from (select CourseId from tblScore where StuId = '1001') a

join tblScore b on a.CourseId = b.CourseId ) e) and StuId <> '1001'; --一门课程用in查询更为简洁;

 

--学过两门以上;两门就等于二;

select stuid, stuname from tblStudent where StuId in (

select stuid from (

select b.* from(select CourseId from tblScore where StuId = '1001') a 

join tblScore b

on a.CourseId = b.CourseId) e  group by StuId having count(distinct CourseId) >= 2) and StuId <> '1001';

 

--学过所有课程呢?

select stuid, stuname from tblStudent where StuId in (

select stuid from (

select b.* from(

select CourseId from tblScore where StuId = '1001') a 

join  tblScore b

on a.CourseId = b.CourseId) e group by StuId having count(distinct CourseId) =

(select count(distinct CourseId) from tblScore where  StuId = '1001'))

and StuId <> '1001';

 

--所学课程完全相同呢(不多也不少)?

select stuid, stuname from tblStudent where StuId in (

select StuId from tblScore where StuId in (

select stuid from (

select b.* from(

select CourseId from tblScore where StuId = '1001') a  join  tblScore b

on a.CourseId = b.CourseId) e group by StuId having count(distinct CourseId) =

(select count(distinct CourseId) from tblScore where  StuId = '1001')

and StuId <> '1001') group by StuId having count(CourseId) =

(select count(distinct CourseId) from tblScore where  StuId = '1001'));

 

课程完全相同,我因为写的是内连接,不能代表学生的所学的所有课程,只能说明学生学的课程与学生‘1001’学的有共同课程,所以在比较完课程数后还需要再次查询符合条件的学生的课程数,第二次进行比较,如果还有相等那就是真正的完全相同了。

 

如有哪里不足之处,请您指出,谢谢您的浏览!

 

上一篇:[源码解析] 深度学习分布式训练框架 horovod (17) --- 弹性训练之容错


下一篇:[源码解析] 深度学习分布式训练框架 horovod (4) --- 网络基础 & Driver