USE AdventureWorks--嵌套子查询 --使用单值SELECT语言的嵌套子查询 SELECT DISTINCT soh.OrderDate, sod.ProductID FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID WHERE OrderDate = '07/01/2001' --练习使用带变量的连接查询 DECLARE @FirstDate smalldatetime SELECT @FirstDate = MIN(OrderDate) FROM Sales.SalesOrderHeader SELECT DISTINCT soh.OrderDate, sod.ProductID FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.OrderDate = @FirstDate--内部查询,由于这里使用了等号,因而内部查询必定只能从一个行中返回一个列.否则将得到运行错误 SELECT DISTINCT soh.OrderDate, sod.ProductID FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.OrderDate = (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader)--使用返回多个值的子查询的嵌套查询,不推荐 SELECT e.EmployeeID, FirstName, LastName FROM HumanResources.Employee e JOIN Person.Contact c ON e.ContactID = c.ContactID WHERE e.EmployeeID IN (SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate)--上面这种类型的查询几乎总是属于可以使用内部联接而非嵌套的SELECT来实现的范畴,例如下面 SELECT e.EmployeeID, FirstName, LastName FROM HumanResources.Employee e JOIN Person.Contact c ON e.ContactID = c.ContactID JOIN HumanResources.JobCandidate jc ON e.EmployeeID = jc.EmployeeID
很实用! 贴切实际!
http://topic.csdn.net/u/20100517/17/B2AB9D5E-73A2-4F54-A7EC-40A5EABD8621.html
USE AdventureWorks--嵌套子查询
--使用单值SELECT语言的嵌套子查询
SELECT DISTINCT soh.OrderDate, sod.ProductID
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE OrderDate = '07/01/2001' --练习使用带变量的连接查询
DECLARE @FirstDate smalldatetime
SELECT @FirstDate = MIN(OrderDate) FROM Sales.SalesOrderHeader SELECT DISTINCT soh.OrderDate, sod.ProductID
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate = @FirstDate--内部查询,由于这里使用了等号,因而内部查询必定只能从一个行中返回一个列.否则将得到运行错误
SELECT DISTINCT soh.OrderDate, sod.ProductID
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate = (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader)--使用返回多个值的子查询的嵌套查询,不推荐
SELECT e.EmployeeID, FirstName, LastName
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID IN
(SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate)--上面这种类型的查询几乎总是属于可以使用内部联接而非嵌套的SELECT来实现的范畴,例如下面
SELECT e.EmployeeID, FirstName, LastName
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.ContactID
JOIN HumanResources.JobCandidate jc
ON e.EmployeeID = jc.EmployeeID