赞
踩
目录
找出没有使用天津产的零件的工程号码:
- -- 7.找出没有使用天津产的零件的工程号码
- -- (遍历工程,不在“该工程有供应商是天津的”工程集合中)
- SELECT JNO
- FROM J
- WHERE JNO NOT IN
- (SELECT JNO
- FROM S,SPJ
- WHERE SPJ.SNO=S.SNO AND S.CITY='天津');
带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。
当我们确定内层循环返回的是单个值时,就可以用>、<、=>、=<、=、!=、<>等比较运算符。
例如,在学生表中,因为一个学生只可能在一个系学习,即内查询的结果是一个值,则可以用=代替IN:
- SELECT Sno,Sname,Sdept
- FROM SC x
- WHERE Sdept =
- (SELECT Sdept
- FROM Student
- WHERE Sname='Cheese');
举个栗子:找出每个学生超过他自己选修课程平均成绩的课程号,如何查询?
思路:选出SC表中的学生各科成绩Grade,与子查询中查询到的由SC表计算产生的平均成绩AVG(Grade)比较。每个学生的各科成绩对应自己的平均成绩。
- SELECT Sno,Cno
- FROM SC x
- WHERE Grade >= (SELECT AVG(Grade)
- FROM SC y
- WHERE y.Sno = x.Sno);
子查询返回单值时可以用比较运算符,但返回多值时要用ANY(有的DBMS用SOME)或ALL修饰符。而使用ANY或ALL谓词时必须同时使用比较运算符。
例如 >ANY 表示大于子查询结果中的某个值,<ALL 表示小于子查询结果中的所有值。
举个栗子1:我们查询 非CS系 中比 CS系 任意一个学生年龄小的学生姓名和年龄。
- SELECT Sname,Sage
- FROM Student
- WHERE Sage < ANY (SELECT Sage
- FROM Student
- WHERE Sdept='CS')
- AND Sdept <> 'CS'; /*这是父查询块的并列条件,不是子查询中的*/
上述查询执行时,首先处理子查询,找出CS系中所有学生的年龄组成一个集合(20,19),然后处理父查询,找出所有不是CS系且年龄小于20或年龄小于19的学生 。查询结果有两个元组,年龄值域为(18,19)。
本查询也可以用聚集函数来实现!首先用子查询找出CS系中最大年龄(20),然后在父查询中找出所有不是CS系且年龄小于20的学生。请读者自己思考并编写语句。(提示:使用聚集函数MAX)
举个栗子2:查询 非CS系 中比 CS系 所有学生年龄小的学生姓名和年龄。
只需要把上述代码的ANY改为ALL即可。查询结果为一个元组,年龄值域为(18)。
只返回逻辑真True或逻辑假False
使用exists引出的子查询,其目标表达式列都使用*,因为带exists的子查询只返回真值或假值,给出列名无实际含义。
如果子查询中因为加了条件而没有结果集返回,则主语句就不执行了。
- #列出选修了C001课程的学生的学号、姓名
- select sno,sname from student
- where exists(
- select * from sc where sc.sno=student.sno and cno='C001'
- );
【查询过程】:从student的第一条记录开始查询,将第一条记录代入子查询中,在sc表中匹配该学生选课记录,若匹配到则立刻返回真,父查询中输出该记录;若匹配完后结果仍为空,否则返回假,继续父查询继续代入下一条记录到子查询中查询。
第一种解读:假设S1供应零件Px(Px不唯一),这个工程用到的Px可以不是S1产的,但此工程必须含有全部的Px。
翻译成可以转化为代码的语句:在 S1 生产的全部零件y中,不存在有一个零件y,工程x不使用。
因为这个语义中,含有两个否定,因此需要两个 `NOT EXISTS` 来完成从自然语言到 SQL 语言的转化。
- select distinct 工程号
-
- from SPJ SPJZ
-
- where not exists ( -- 4. 它不使用
-
- select * -- 1. 在 S1 生产的全部零件中,
-
- from SPJ SPJX
-
- where SNO = 'S1'
-
- and not exists ( -- 2. 不存在
-
- select * -- 3. 有一个零件
-
- from SPJ SPJY
-
- where SPJY.零件号 = SPJX.零件号 and SPJY.工程号 = SPJZ.工程号
-
- )
-
- );
需要运用相关子查询
第二种解读:假设S1供应零件Px(Px不唯一),这个工程必须用到所有的Px,且所有的Px也必须是S1产的。所以不存在这样的零件y,S1供应了y,而工程x没有选用y;或工程x选用了y,但选用的y不只是S1供应的。
翻译成可以转化为代码的语句:在 S1 生产的全部零件y中,不存在有一个零件y,工程x不使用,且不来自S1。
在MySQL8.0之后可以用差运算符EXCEPT来处理这个问题:
EXCEPT 操作符用于返回出现在第一个查询结果中,但不在第二个查询结果中的记录,并且对最终结果进行了去重操作。
参考视频: SELECT嵌套查询和集合查询
赞
踩
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。