数据库知识点（5）——多列过滤

多列过滤是在where语句后用and或or连接对列进行过滤的条件，从而筛选出符合条件的数据。

此篇以学生成绩为例进行演示。

涉及的表有student（学生信息表）和stuscore(成绩表)如图：

1、查询语文优秀（85-100）的学生信息

使用关联查询

 select a.no,a.name,b.subject,b.score from student a,stuscore b  
where a.no = b.stuno and b.subject='语文' and( b.score between 85 and 100);

使用子查询

select a.no,a.name,b.subject,b.score from student a join stuscore b on a.no = b.stuno  where (stuno,score) in (select stuno,score from stuscore where b.subject='语文' and( b.score between 85 and 100) );

结果

2、查询语文或英语有一科优秀的学生信息

 select a.no,a.name,b.subject,b.score from student a  
       join stuscore b on a.no = b.stuno  
       where (stuno,score) in
       (select stuno,score from stuscore
		 where (b.subject='语文' and( b.score between 85 and 100)) or  
		       (b.subject='英语' and( b.score between 85 and 100)))  ;

3、查询语文和英语都优秀的学生信息

 select a.no,a.name,b.subject,b.score from student a  
       join stuscore b on a.no = b.stuno  
       where (stuno,score) in
       (select stuno,score from stuscore 
		where  (b.subject='语文' and( b.score between 85 and 100)) and 
		       (b.subject='英语' and( b.score between 85 and 100))) ;

优先级是先括号中的内容，在and，在or