连接子查询

数据库：|AdventureWorks；

功能需求：查询所有同时订购了商品名为：'HL Mountain Front Wheel’和'HL Mountain Rear Wheel'的客户信息；

实现逻辑：利用连接子查询和连接查询（join）的语法来实现；

代码：

 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;