SQL SERVER 2005 高级查询（子查询查询）
--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 DISTI
相关文档：
如果你经常遇到下面的问题，你就要考虑使用SQL Server的模板来写规范的SQL语句了：
SQL初学者。
经常忘记常用的DML或是DDL SQL 语句。
在多人开发维护的SQL中，每个人都有自己的SQL习惯，没有一套统一的规范。
在SQL Server Management Studio中，已经给大家提供了很多常用的现成SQL规范模板。
SQL Server Management ......
（18）用EXISTS替换DISTINCT：
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。例子：
(低效):
SELECT DISTINCT DEPT_NO,DEPT_NAME ......
thunder:
1.MYSQL实现
mysql> select * from user;
+----+----------+----------+-----------------+
| ID | username | password | email |
+----+----------+----------+-----------------+
| 1 | admin | admin &nb ......
1.创建数据库
--exec xp_cmdshell 'mkdir d:\project'--调用DOS命令创建文件夹，使用此句需要启动SQL的外围工具
if exists(select * from sysdatabases where name='数据库名')
drop database 数据库名
set nocount on ......
use AdventureWorks
GO
SELECT c.LastName from Person.Contact c;
SELECT * from HumanResources.Employee e
INNER JOIN HumanResources.Employee m
ON e.ManagerID = m.EmployeeID; n
SELECT ProductID,Name,ProductNumber,ReorderPoint
from Production.Product
where ProductID in( select ProductID fro ......
