資料庫知識點（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