SQL SERVER 2005 高级查询（子查询查询）

--SQL高级程序设计：子查询use AdventureWorksGOSELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL;SELECT e.EmployeeID,FirstName,LastNameFROM HumanResources.Employee eINNER JOIN Person.Contact c ON e.ContactID = c.ContactIDWHERE e.EmployeeID IN (SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL) SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate WHERE EmployeeID IS NOT NULLSELECT e.EmployeeID,FirstName,LastNameFROM HumanResources.Employee eINNER JOIN Person.Contact c ON e.ContactID = c.ContactIDWHERE e.EmployeeID NOT IN ( SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL )SELECT CustomerID,MIN((OrderDate)) AS OrderDateINTO #MinOrderDatesFROM Sales.SalesOrderHeaderGROUP BY CustomerIDORDER BY CustomerIDSELECT o.CustomerID,o.SalesOrderID,o.OrderDatefrom Sales.SalesOrderHeader oinner join #MinOrderDates t on o.CustomerID = t.CustomerID and o.OrderDate = t.OrderDateorder by o.CustomerIDDROP TABLE #MinOrderHeadersSELECT O1.CustomerID,o1.SalesOrderID,o1.OrderDateFROM Sales.SalesOrderHeader o1WHERE o1.OrderDate = (SELECT MIN(o2.OrderDate) FROM Sales.SalesOrderHeader o2 WHERE O2.CustomerID = o1.CustomerID)ORDER BY CustomerIDSELECT c.LastName,ISNULL(CAST((SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader o WHERE o.ContactID = c.ContactID) AS VARCHAR),'NEVER RECORD') AS "Order Date"FROM Person.Contact cSELECT e.EmployeeID,FirstName,LastNameFROM HumanResources.Employee eINNER JOIN Person.Contact c ON e.ContactID = c.ContactIDWHERE EXISTS(SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate jc WHERE EmployeeID = e.EmployeeID)IF NOT EXISTS(SELECT 'True' FROM SYS.DATABASES WHERE name = 'DBCreateTest')BEGIN CREATE DATABASE DBCreateTestENDELSEBEGIN PRINT 'DATABASE ALREADY EXISTS.'ENDGOSELECT OrderDate,CONVERT(CHAR(12),OrderDate,112) AS "Converted"FROM Sales.SalesOrderHeaderWHERE SalesOrderID = '43660'