子查询
SQL支持在查询语句中编写查询,或者嵌套其他查询.
最外层查询的结果集会返回给调用者,称为外部查询.
内部查询的结果是供外部查询使用的,也称为子查询.
子查询可以分为独立子查询和相关子查询.独立子查询不依赖于它所属的外部查询,而相关子查询则须依赖于它所属的外部查询.
子查询可以返回一个单独的值(标量),多个值或整个表结果.
4.1 独立子查询
独立子查询是独立于其外部查询的子查询.独立子查询调试起来非常方便,因为总可以把子查询代码独立出来单独运行,并确保它能够正确实现默认的功能.
在逻辑上,独立子查询在执行外部查询之前只要先执行一次,接着外部查询再使用子查询的结果继续进行查询.
4.1.1 独立标量子查询
标量子查询是返回单个值的子查询,而不管它是不是独立子查询.
变量的方法:
-- Order with the maximum order ID --以下,返回order表中订单ID最大的订单信息. --通过一个变量,用代码从order表中取出最大的订单ID,再把结果保存在变量中,然后在查询! USE TSQLFundamentals2008; DECLARE @maxid AS INT = (SELECT MAX(orderid) FROM Sales.Orders); SELECT orderid, orderdate, empid, custid FROM Sales.Orders WHERE orderid = @maxid; GO --声明变量 declare, --DECLARE @maxid AS INT = (SELECT MAX(orderid) FROM Sales.Orders);----2008的写法! --2005 的必须单独使用:declare,set --declare @maxid as int ; --set @maxid = ( select max(orderid) from Sales.Orders ); --2005的方式: use TSQLFundamentals2008; declare @maxid as int ; set @maxid = ( select max(orderid) from Sales.Orders ); select orderid,orderdate,empid,custid from Sales.Orders where orderid = @maxid;
通过子查询的方法:
--通过子查询的方法(独立标量子查询),也可以返回表order中订单ID最大的订单信息. USE TSQLFundamentals2008; SELECT orderid, orderdate, empid, custid FROM Sales.Orders WHERE orderid = (SELECT MAX(O.orderid) FROM Sales.Orders AS O);
独立标量子查询注意点:
--注:对于有效的标量子查询,它的返回值不能超过一个.如果标量子查询返回了多个值,在运行时则可能会失败 -- 独立的标量子查询 运行的条件: -- 以下查询的目的是返回由姓氏(lastname)以字母B开头的雇员处理过的订单ID.但是,子查询返回所有姓氏以字母B开头的雇员ID,外部查询则返回雇员ID与子查询结果相等的订单的ID. -- --因为等号运算符期望它左右两边都是单值表达式,所有它会认为其右边的子查询是标量的.因为子查询潜在地可能返回多个值,所以这里选择使用等号运算符和标量子查询是错误的.如果子查询返回了多个值,查询将失败. -- 以下子查询:(SELECT E.empid FROM HR.Employees AS E WHERE E.lastname LIKE N'B%');只返回一个值,所以可以运行的 SELECT orderid FROM Sales.Orders WHERE empid = (SELECT E.empid FROM HR.Employees AS E WHERE E.lastname LIKE N'B%'); GO -- 以下就会报错,因为它的独立子查询,返回的值有两个! SELECT orderid FROM Sales.Orders WHERE empid = (SELECT E.empid FROM HR.Employees AS E WHERE E.lastname LIKE N'D%'); GO -- 如果标量子查询没有返回任何值,其结果就转换为NULL! -- 以下子查询没有任何返回值 SELECT orderid FROM Sales.Orders WHERE empid = (SELECT E.empid FROM HR.Employees AS E WHERE E.lastname LIKE N'A%');
4.1.2 独立多值子查询
多值子查询是在一个列中返回多个值的子查询,而不管子查询是不是独立的.
--in谓词 SELECT orderid FROM Sales.Orders WHERE empid IN (SELECT E.empid FROM HR.Employees AS E WHERE E.lastname LIKE N'D%');
--以上和以下,类似地,可能会遇到很多其他既可以用子查询,又可以用联接来解决的问题. --一些情况下,数据库对这两种查询的解释是一样的;另一些情况下,对二者的解释则是不同的 --方法:对于给定的任务,先用直观的形式写出能解决问题的查询语句;如果对它的运行的性能不满意,调整方法之一就是尝试重构查询.这样的查询重构可以包括用联接取代子查询,或者用子查询取代联接. SELECT O.orderid FROM HR.Employees AS E JOIN Sales.Orders AS O ON E.empid = O.empid WHERE E.lastname LIKE N'D%';
-- Orders placed by US customers -- 返回由美国客户下的所有订单 SELECT custid, orderid, orderdate, empid FROM Sales.Orders WHERE custid IN (SELECT C.custid FROM Sales.Customers AS C WHERE C.country = N'USA'); -- Customers who placed no orders -- 和其他谓词一样,可以使用NOT逻辑运算符来否定IN谓词. -- 注:为了遵循最佳实践,在子查询返回的结果中应该排除NULL值! SELECT custid, companyname FROM Sales.Customers WHERE custid NOT IN (SELECT O.custid FROM Sales.Orders AS O); GO --注:在子查询中指定一个DISTINCT子句是否会有助于提高性能?因为相同的客户ID在Orders表中可能出现多次.数据库引擎足够聪明,它指定应该删除重复的值,而不必非要显式地要求它这么做,所以这个问题就不必我们多费心了.
-- Missing order IDs -- 一条查询语句中同时使用多个独立子查询(既有单值查询,也有多值查询) USE tempdb; IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders; GO -- select into语句,用于创建一个目标表,并用查询的结果集来填充该表 SELECT * INTO dbo.Orders FROM TSQLFundamentals2008.Sales.Orders WHERE orderid % 2 = 0; SELECT n FROM dbo.Nums WHERE n BETWEEN (SELECT MIN(O.orderid) FROM dbo.Orders AS O) AND (SELECT MAX(O.orderid) FROM dbo.Orders AS O) AND n NOT IN (SELECT O.orderid FROM dbo.Orders AS O); -- CLeanup 清理测试数据 DROP TABLE tempdb.dbo.Orders;
4.2 相关子查询
相关子查询是指引用了外部查询中出现的表的列的子查询.这意味着子查询要依赖于外部查询,不能独立地调用它.
在逻辑上,子查询会为每个外部行单独计算一次.
--------------------------------------------------------------------- -- Correlated Subqueries --------------------------------------------------------------------- -- Orders with maximum order ID for each customer USE TSQLFundamentals2008; --以下为一个相关子查询 --1.外部查询对Orders表的一个名为O1的实例进行查询,由它筛选出订单ID等于子查询返回值的订单. --2.子查询从Orders表的另一个名为O2的实例筛选出内部客户ID等于外部客户ID的订单,并返回筛选出的订单中最大的订单ID. --理解:对于O1中的每一行,子查询负责返回当前客户的最大订单ID.如果O1中某行的订单ID和子查询返回的订单ID匹配,那么O1中的这个订单ID就是当前客户的最大订单ID,在这种情况下,查询便会返回O1表中的这个行 SELECT custid, orderid, orderdate, empid FROM Sales.Orders AS O1 WHERE orderid = (SELECT MAX(O2.orderid) FROM Sales.Orders AS O2 WHERE O2.custid = O1.custid); --相关子查询通常比独立子查询要难理解.一种有用的方法:将注意力集中在外部表的某一行,再来理解针对改行所进行的逻辑处理. --以下,就是查找出客户为85的最大订单ID号,然后在与外部行进行比较,匹配的话,说明该外部行的订单ID就是当前客户的最大订单ID SELECT MAX(O2.orderid) FROM Sales.Orders AS O2 WHERE O2.custid = 85; --可以转换成多值的独立子查询,先找到每个客户的最大订单ID,然后在通过这个ID值来匹配出来 SELECT custid, orderid, orderdate, empid FROM Sales.Orders where orderid in( SELECT max(orderid) FROM Sales.Orders group by custid) order by custid asc --另一个相关子查询 -- Percentage of store total SELECT orderid, custid, val, CAST(100. * val / (SELECT SUM(O2.val) FROM Sales.OrderValues AS O2 WHERE O2.custid = O1.custid) AS NUMERIC(5,2)) AS pct FROM Sales.OrderValues AS O1 ORDER BY custid, orderid;
4.2.1 EXISTS谓词
支持一个名为EXISTS谓词,它的输入是一个子查询;如果子查询能够返回任何行,该谓词则返回TRUE,否则返回FALSE.
--------------------------------------------------------------------- -- EXISTS --------------------------------------------------------------------- -- Customers from Spain who placed orders SELECT custid, companyname,country FROM Sales.Customers AS C WHERE country = N'Spain' AND EXISTS (SELECT * FROM Sales.Orders AS O WHERE O.custid = C.custid); --以上,对Customers表的外部查询只筛选出来自Spain和EXISTS谓词为TRUE(如果当前客户在Orders表中有相关的订单). --日常英文解释:从Customers表中选择一个客户ID和公司列名称,要求该客户的国家等于Spain,而且在Orders表中至少存在一个订单满足订单的客户ID和该客户的客户ID相同 -- Customers from Spain who didn't place Orders --和其他谓词一样,可以用NOT逻辑运算符来否定EXISTS谓词. SELECT custid, companyname FROM Sales.Customers AS C WHERE country = N'Spain' AND NOT EXISTS (SELECT * FROM Sales.Orders AS O WHERE O.custid = C.custid); --EXISTS! --对于EXISTS谓词使用,在其子查询的SELECT列表中使用星号(*)在逻辑上并不是一种不好的实践方式. --EXISTS谓词只关心是否存在匹配行,而不考虑SELECT列表中指定的列,好像整个SELECT子句是多余的... --观点:查询语句应该尽可能保持自然和直观,除非有非常令人信服的理由,才可以牺牲代码在这方面的要求. --书中的观点:EXISTS(SELECT * FROM ...)这种形式比 EXISTS(SELECT 1 FROM ...)要直观得多.与牺牲代码的可读性而付出的代价相比,由解析通配符(*)带来的额外开销是次要的,不值得为节省这点开销而牺牲代码的可读性. --EXISTS谓词注意点:EXISTS谓词使用的是二值逻辑,而不是三值逻辑.
4.3 高级子查询
4.3.1 返回前一个或后一个记录
use TSQLFundamentals2008 --Order表,对于每个订单,返回当前订单的信息和它的前一个订单的ID.逻辑概念就是:小于当前值的最大值! --prevorderid 当前订单的前一个订单的orderid,子查询为第一个订单返回一个null值. SELECT orderid, orderdate, empid, custid, (SELECT MAX(O2.orderid) FROM Sales.Orders AS O2 WHERE O2.orderid < O1.orderid) AS prevorderid FROM Sales.Orders AS O1; --返回当前订单信息和它的下一个订单的ID.逻辑概念:大于当前值的最小值! --nextorderid 当前订单的下一个订单的orderid,子查询为最后一个订单返回一个NULL值. SELECT orderid, orderdate, empid, custid, (SELECT MIN(O2.orderid) FROM Sales.Orders AS O2 WHERE O2.orderid > O1.orderid) AS nextorderid FROM Sales.Orders AS O1;
4.3.2 连续聚合(Running Aggregate)
连续聚合是一种对累积数据(通常是按时间顺序)执行的聚合.
--------------------------------------------------------------------- -- Running Aggregates --------------------------------------------------------------------- SELECT orderyear, qty FROM Sales.OrderTotalsByYear; --需求:返回每年的订单年份,订货量,以及连续几年的总订货量(每一年返回截止到该年的订货量的总和). --思路: --1.先对这个视图的一个实例(称为o1)进行查询,返回每一年的订单年份和订货量. --2.再对这个视图的另一个实例(称为o2)使用相关子查询,计算连续的总订货量. --注:子查询应该在o2中筛选出所有订单年份小于或等于o1中当前年份的所有行,并计算o2中这些行的订货量之和. SELECT orderyear, qty, (SELECT SUM(O2.qty) FROM Sales.OrderTotalsByYear AS O2 WHERE O2.orderyear <= O1.orderyear) AS runqty FROM Sales.OrderTotalsByYear AS O1 ORDER BY orderyear;
4.3.3 行为不当(Misbehaving)的子查询
null的问题
****当对至少返回一个NULL值的子查询使用NOT IN谓词时,外部查询总会返回一个空集.