--SQL高级程序设计:子查询 use AdventureWorks GO SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL; SELECT e.EmployeeID,FirstName,LastName FROM HumanResources.Employee e INNER JOIN Person.Contact c ON e.ContactID = c.ContactID WHERE e.EmployeeID IN (SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL) SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL SELECT e.EmployeeID,FirstName,LastName FROM HumanResources.Employee e INNER JOIN Person.Contact c ON e.ContactID = c.ContactID WHERE e.EmployeeID NOT IN ( SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL ) SELECT CustomerID,MIN((OrderDate)) AS OrderDate INTO #MinOrderDates FROM Sales.SalesOrderHeader GROUP BY CustomerID ORDER BY CustomerID SELECT o.CustomerID,o.SalesOrderID,o.OrderDate from Sales.SalesOrderHeader o inner join #MinOrderDates t on o.CustomerID = t.CustomerID and o.OrderDate = t.OrderDate order by o.CustomerID DROP TABLE #MinOrderHeaders SELECT O1.CustomerID,o1.SalesOrderID,o1.OrderDate FROM Sales.SalesOrderHeader o1 WHERE o1.OrderDate = (SELECT MIN(o2.OrderDate) FROM Sales.SalesOrderHeader o2 WHERE O2.CustomerID = o1.CustomerID) ORDER BY CustomerID SELECT 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 c SELECT e.EmployeeID,FirstName,LastName FROM HumanResources.Employee e INNER JOIN Person.Contact c ON e.ContactID = c.ContactID WHERE 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 DBCreateTest END ELSE BEGIN PRINT 'DATABASE ALREADY EXISTS.' END GO SELECT OrderDate,CONVERT(CHAR(12),OrderDate,112) AS "Converted" FROM Sales.SalesOrderHeader WHERE SalesOrderID = '43660'