子查询:
将一个查询分解为一系列的步骤。
提供一个列表作为WHERE子句和[IN|EXISTS|ANY|ALL]的目标
为父查询中的每一个记录提供一个查询表
关联子查询:
外部查询获得一个记录,然后将该记录传递到内部查询。
内部查询根据传递的值执行。
然后内部查询将结果值传回外部查询,而外部查询利用这些值完成处理过程。
在WHERE子句中的关联子查询:
使用临时表:
SELECT soh.CustomerID, MIN(soh.OrderDate) AS OrderDate
INTO #MinOrderDates
FROM Sales.SalesOrderHeader soh
GROUP BY soh.CustomerID;
-- Do something additional with that information
SELECT soh.CustomerID, soh.SalesOrderID, soh.OrderDate
FROM Sales.SalesOrderHeader soh
JOIN #MinOrderDates t
ON soh.CustomerID = t.CustomerID
AND soh.OrderDate = t.OrderDate
ORDER BY soh.CustomerID;
DROP TABLE #MinOrderDates;
使用关联子查询:
SELECT soh1.CustomerID, soh1.SalesOrderID, soh1.OrderDate
FROM Sales.SalesOrderHeader soh1
WHERE soh1.OrderDate = (SELECT Min(soh2.OrderDate)
FROM Sales.SalesOrderHeader soh2
WHERE soh2.CustomerID = soh1.CustomerID)
ORDER BY CustomerID;
在SELECT列表中的关联子查询:
SELECT sc.AccountNumber,
(SELECT Min(OrderDate)
FROM Sales.SalesOrderHeader soh
WHERE soh.CustomerID = sc.CustomerID)
AS OrderDate
FROM Sales.Customer sc;
处理NULL数据——ISNULL函数:
SELECT sc.AccountNumber,
ISNULL(CAST((SELECT MIN(OrderDate)
FROM Sales.SalesOrderHeader soh
WHERE soh.CustomerID=sc.CustomerID) AS varchar),'NEVER ORDERED')
AS OrderDate
FROM Sales.Customer sc;
注:必须使用CAST()来完成这个工作。原因是要处理强制转换和饮食转换。
派生表:
创建派生表:
将生成结果集的查询用小括号括起来。
给查询结果取别名,这样他可以作为表被引用。
例:
SELECT DISTINCT sc.AccountNumber, sst.Name
FROM Sales.Customer AS sc
JOIN Sales.SalesTerritory sst
ON sc.TerritoryID = sst.TerritoryID
JOIN
(SELECT CustomerID
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product pp
ON sod.ProductID = pp.ProductID
WHERE pp.Name = 'HL Mountain Rear Wheel') AS dt1
ON sc.CustomerID = dt1.CustomerID
JOIN
(SELECT CustomerID
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product pp
ON sod.ProductID = pp.ProductID
WHERE Name = 'HL Mountain Front Wheel') AS dt2
ON sc.CustomerID = dt2.CustomerID;
使用通用表达式(CTE):
使用ETC:
使用多个ETC:
递归ETC:
使用EXISTS运算符:
使用EXISTS筛选数据:
SELECT BusinessEntityID, LastName + ', ' + FirstName AS Name
FROM Person.Person pp
WHERE EXISTS
(SELECT BusinessEntityID
FROM HumanResources.Employee hre
WHERE hre.BusinessEntityID = pp.BusinessEntityID);
混合数据类型:CAST和CONVERT
CAST是ANSI兼容的,而CONVERT不兼容。
CAST例:
SELECT 'The Customer has an Order numbered ' + CAST(SalesOrderID AS varchar)
FROM Sales.SalesOrderHeader
WHERE CustomerID = 29825;
SELECT ColTS AS Uncoverted, CAST(ColTS AS int) AS Converted
FROM ConvertTest;
SELECT OrderDate, CAST(OrderDate AS varchar) AS Converted
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 43663;
CONVERT例:
SELECT OrderDate, CONVERT(varchar(12), OrderDate, 111) AS Converted
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 43663;
SELECT OrderDate, CONVERT(varchar(12), OrderDate, 5) AS Converted
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 43663;
注:111给出日本标准,带4位数字的年份;5是意大利标准,带俩位数字年份。超过100的代表4位数字年份;小于100代表两位数字年份。