【2017-3-12】SQL Server 子查询
子查询:
把一条查询语句(子句),当做值来使用
子句 的查询结果必须是一列
子句可以返回多行数据,但必须是一列
select * from car --查询所有car表
--我只知道一个汽车编号 c021
--查询 价格 高于这个汽车编号的 所有汽车信息
select * from car --查询所有车辆
select price from car where code = 'c021' --查询code为c021的车辆价格
select * from car where price > (select price from car where code = 'c021')
--查询油耗与c016相等的,或者与c029相等的,或者与c014相等的 全部汽车信息
select * from car where oil =() --查询油耗等于谁的所有车辆信息
select oil from car where code = 'c016' or code = 'c029' or code = 'c014' --子句
--在这几个值里面用 in 不在则用 not in
select * from car where oil in(select oil from car where code = 'c016' or code = 'c029' or code = 'c014')
select * from car where oil >= 7 and oil <= 8
select * from car where oil between 7 and 8
--查询所有汽车信息,条件是油耗大于括号内 任意 一个数 大于任意最小的,小于任意最大的
select * from car where oil >any (select oil from car where code = 'c016' or code = 'c029' or code = 'c014')
--查询所有汽车信息,条件是油耗大于括号内 所有 数 大于所有最大的,小于所有最小的
select * from car where oil >all (select oil from car where code = 'c016' or code = 'c029' or code = 'c014')
--表连接
select name,(select brand_name from brand where car.brand = brand.brand_code) from car
-------------------------------------------------------------------------------------------------------
练习