SQL Server进阶（五）子查询

概述

子查询的概念：

　　子查询就是嵌套在主查询中的查询。

　　子查询可以嵌套在主查询中所有位置，包括SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。

子查询按照相关性分类

1.相关子查询

　　必须依赖于它所属的外部查询，不能独立地调用它。

　　外部查询返回一行，子查询就执行一次。

2.非相关子查询

　　独立于外部查询的子查询。

　　子查询总共执行一次，执行完毕后后将值传递给外部查询。

　　需要注意的是相关子查询主查询执行一回，子查询就执行一回，十分耗费时间，尤其是当数据多的时候。

子查询按照返回的结果集分类

1.单值子查询

　　只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。当子查询跟随在=、!=、<、<=、>、>=，<> 之后，或子查询用作表达式，只能使用单值子查询。

2.多值子查询

　　如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合，那么可以使用in关键字代替=号。

Exists原理

　　exists做为where 条件时，是先对where 前的主查询询进行查询，然后用主查询的结果一个一个的代入exists的查询进行判断，如果为真则输出当前这一条主查询的结果，否则不输出。

　　查询时，一般情况下，子查询会分成两种情况：

1.子查询与外表的字段有关系时

select 字段1 , 字段2 from 表1 where exists (select 字段1 , 字段2 from 表2 where 表2.字段2 = 表1.字段2)

　　这时候，此SQL语句相当于一个关联查询。

　　它先执行表1的查询，然后把表1中的每一条记录放到表2的条件中去查询，如果存在，则显示此条记录。

2.子查询与外表的字段没有任何关联

Select 字段1 , 字段2 from 表1 where exists ( select * from 表2 where 表2.字段 = ‘ 条件‘)

　　在这种情况下，只要子查询的条件成立，就会查询出表1中的所有记录，反之，如果子查询中没有查询到记录，则表1不会查询出任何的记录。

　　当子查询与主表不存在关联关系时，简单认为只要exists为一个条件判断，如果为true,就输出所有记录。如果为false则不输出任何的记录。

自包含子查询

自包含标量子查询

DECLARE @maxid AS INT = (SELECT MAX(orderid)
                         FROM Sales.Orders);

SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderid = @maxid;

SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderid = (SELECT MAX(O.orderid)
                 FROM Sales.Orders AS O);

自包含多值子查询

SELECT orderid
FROM Sales.Orders
WHERE empid = 
  (SELECT E.empid
   FROM HR.Employees AS E
   WHERE E.lastname LIKE N'B%');

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);

相关子查询

什么是相关子查询：引用了外部查询中出现的表的列，依赖于外部查询，不能独立地运行子查询。在逻辑上，子查询会为每个外部行单独计算一次。

例子1：查询每个客户返回在他参与活动的最后一天下过的所有订单。

期望结果：

SQL Server进阶（五）子查询

影响行数：90

1.首先用独立标量子查询查询出最大的订单日期，返回给外部查询

SELECT  MAX(orderdate)
FROM    sales.Orders AS O2

2.外部查询用O1.orderdate进行过滤，过滤出等于最大订单日期的订单

3.因为要查询出每个客户参与的订单，所以将独立标量子查询改成相关子查询，用子查询O2.custid与外查询O1.custid关联。

对于O1中每一行，子查询负责返回当前客户的最大订单日期。如果O1中某行的订单日期和子查询返回的订单日期匹配，那么O1中的这个订单日期就是当前客户的最大的订单日期，在这种情况下，查询便会返回O1表中的这个行。

SELECT  MAX(orderdate)
FROM    sales.Orders AS O2
WHERE   O2.custid = O1.custid

综合上面的步骤，得到下面的查询语句：

SELECT  orderid,orderdate,custid
FROM    sales.Orders AS O1
WHERE   O1.orderdate = ( SELECT MAX(orderdate)
                         FROM   sales.Orders AS O2
                         WHERE  O2.custid = O1.custid

例子2：为每个客户返回最大订单ID的订单。

第一步：

SELECT MAX(O2.orderid)
FROM Sales.Orders AS O2

第二步：

SELECT MAX(O2.orderid)
   FROM Sales.Orders AS O2
   WHERE O2.custid = O1.custid

第三步：

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);

Exists谓词

它的输入是一个查询，如果子查询能够返回任何行，则返回True，否则返回False。

返回下订单的西班牙客户

SELECT custid, companyname
FROM Sales.Customers AS C
WHERE country = N'Spain'
  AND EXISTS
    (SELECT * FROM Sales.Orders AS O
     WHERE O.custid = C.custid);

返回没有下订单的西班牙客户

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，它采用的是二值逻辑（TRUE和FALSE），它只关心是否存在匹配行，而不考虑SELECT列表中指定的列，并且无须处理所有满足条件的行。可以将这种处理方式看做是一种“短路”，它能够提高处理效率。　

　　另外，由于EXISTS采用的是二值逻辑，因此相较于IN要更加安全，可以避免对NULL值得处理。　

高级子查询

如何表示前一个或后一个记录？逻辑等式：上一个->小于当前值的最大值；下一个->大于当前值的最小值；

-- 上一个订单ID
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;

如何实现连续聚合函数？在子查询中连续计算

-- 连续聚合
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;

SQL Server进阶（五）子查询

使用NOT EXISTS谓词取代NOT IN隐式排除NULL值：当对至少返回一个NULL值的子查询使用NOT IN谓词时，外部查询总会返回一个空集。（前面提到，EXISTS谓词采用的是二词逻辑而不是三词逻辑）

-- 隐式排除NULL值
select custid,companyname from sales.Customers as c
where not exists
(select * 
 from sales.Orders as o
 where o.custid=c.custid);

SQL Server进阶（五）子查询

又如以下查询请求返回每个客户在2007年下过订单而在2008年没有下过订单的客户：

select custid, companyname
from sales.Customers as c
where exists 
(select * from sales.Orders as o1
 where c.custid=o1.custid 
 and o1.orderdate>='20070101' and o1.orderdate<'20080101')
and not exists 
(select * from sales.Orders as o2
 where c.custid=o2.custid
 and o2.orderdate>='20080101' and o2.orderdate<'20090101');

SQL Server进阶（五）子查询

收集资料

https://blog.csdn.net/qq_26937525/article/details/53930498

http://www.cnblogs.com/jackson0714/p/TSQLFundamentals_03.html

https://blog.csdn.net/mascf/article/details/50288199

秒客网

SQL Server进阶（五）子查询

概述

Exists原理

自包含子查询

自包含标量子查询

自包含多值子查询

相关子查询

Exists谓词

高级子查询

收集资料

相关文章