1.使用IN：查询班级1，班级2，班级3中1983年出生的同学
2.使用NOT：查询数学成绩不及格且不在班级1，班级2中的同学
3.使用ALL：查询班级1中数学成绩比学生1和学生2高的所有同学
4.使用ANY：查询班级1中比学生1或学生2年龄大的同学
T－SQL及查询结果
1.
查询班级1，班级2，班级3中1983年出生的同学(in)
select
s.stu_name as 姓名,
year(s.stu_birth) as 出生年份,
c.class_name as 班级
from tab_students as s
inner join tab_student_class as sc on s.stu_id=sc.stu_id
inner join tab_classes c on sc.class_id=c.class_id
where c.class_name in ('班级1','班级2','班级3') and year(s.stu_birth)='1983'
go
2.
查询数学成绩不及格且不在班级1，班级2中的同学(not in)
select
c.class_name as 班级,
s.stu_name as 姓名,
ss.score 数学成绩
from tab_students as s
inner join tab_student_class as sc on s.stu_id=sc.stu_id
inner join tab_classes c on sc.class_id=c.class_id
inner join tab_student_Score as ss on s.stu_id=ss.stu_id
inner join tab_subject su on ss.subject_id=su.subject_id
where c.class_name not in ('班级1','班级2') and su.subject_name='数学' and ss.score<60
go
3.
查询班级1中数学成绩比学生1和学生2高的所有同学(all)
select
c.class_name as 班级,
s.stu_name as 姓名,
ss.score as 分数
from tab_students as s
inner join tab_student_score as ss on s.stu_id=ss.stu_id
inner join tab_student_class as sc on s.stu_id=sc.stu_id
inner join tab_classes as c on sc.class_id=c.class_id
inner join tab_subject as su on ss.subject_id=su.subject_id
where ss.score > all
(select score
from tab_student_score as ss1
inner join tab_students as s1 on ss1.stu_id=s1.stu_id
inner join tab_subject as su1 on ss1.subject_id=su1.subject_id
inner join tab_student_class as sc1 on s1.stu_id=sc1.stu_id
inner join tab_classes as c1 on sc1.class_id=c1.class_id
where s1.stu_name in('学生1','学生2')
and c1.class_name='班级1'
and su1.subject_name='数学')
and c.class_name='班级1'
and su.subject_name='数学'
go
4.
查询班级1中比学生1或学生2年龄大的同学(any)
select
c.class_name as 班级,
s.stu_name as 姓名,
s.stu_birth as 出生年月
from tab_students as s
inner join tab_student_class as sc on s.stu_id=sc.stu_id
inner join tab_classes as c on sc.class_id=c.class_id
where s.stu_birth < any
(select s1.stu_birth
from tab_students as s1
inner join tab_student_class as sc1 on s1.stu_id=sc1.stu_id
inner join tab_classes as c1 on sc1.class_id=c1.class_id
where s1.stu_name in('学生1','学生2')
and c1.class_name='班级1')
and c.class_name='班级1'
go
