表结构与数据:https://github.com/XuePeng87/TSQLV4
子查询
1.自包含子查询
每个子查询都具有一个其所隶属的外部查询。自包含子查询是独立于其所隶属的外部查询的。看一个自包含子查询示例:
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderid = (SELECT MAX(O.orderid) FROM Sales.Orders AS O)
使用"="的话,子查询如果返回多行则会使查询失败。若子查询返回NULL,与NULL比较会生成UNKNOWN,查询筛选不返回拆选表达式计算为NUKNOWN的行。
如果子查询返回的是多行,可以使用IN处理,例如:
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE empid IN
(
SELECT E.empid
FROM HR.Employees AS E
WHERE E.lastname LIKE N'D%'
);
2.相关子查询
相关子查询引用的表位于外部查询中,这意味该子查询依赖于外部查询并且无法单独调用。例如:
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
);
上面例子中,子查询使用了外部表O1的字段,所以是相关子查询。
T-SQL支持一个叫做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
);
EXISTS使用的是两值逻辑,而不是三值逻辑。
子查询应用
返回前一个或下一个值
为每个订单返回当前订单信息,以及前一个订单的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;