Oracle中的查询最能提升一个人的思维,一道题目可以有不同的方法进行解决,先来看下例子,如下:
题目:查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
先看下表结构以及部分表数据,如下图:
上面这个题目很简单,因为只有一门相同课程就可以,先查询出同学’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’学的有共同课程,所以在比较完课程数后还需要再次查询符合条件的学生的课程数,第二次进行比较,如果还有相等那就是真正的完全相同了。
如有哪里不足之处,请您指出,谢谢您的浏览!