多列过滤是在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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/1727/viewspace-2798917/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
啦啦啦啦员工工资合计代码
请登录后发表评论
登录
全部评论
