多种联结语句

-----内联结3个表返回产品的折扣信息-----


SELECT p.Name,s.DiscountPct from Sales.SpecialOffer s

inner JOIN Sales.SpecialOfferProduct o ON

s.SpecialOfferID=o.SpecialOfferID

INNER JOIN Production.Product p ON

o.ProductID=p.ProductID

WHERE p.Name ='All-Purpose Bike Stand'


-----外联结2个表返回税率状态、省-----

SELECT s.CountryRegionCode,s.StateProvinceCode,t.TaxType,t.TaxRate FROM Person.StateProvince s

LEFT OUTER JOIN  Sales.SalesTaxRate t ON

s.StateProvinceID=t.StateProvinceID

-----比较内联结-----
SELECT s.CountryRegionCode,s.StateProvinceCode,t.TaxType,t.TaxRate FROM Person.StateProvince s

INNER JOIN  Sales.SalesTaxRate t ON

s.StateProvinceID=t.StateProvinceID


-----交叉联结-----

SELECT s.CountryRegionCode,s.StateProvinceCode,t.TaxType,t.TaxRate FROM Person.StateProvince s

CROSS JOIN  Sales.SalesTaxRate t


-----实现自联结-----

SELECT e.EmployeeID,e.Title,m.Title AS  ManagerTitle

from HumanResources.Employee e

LEFT OUTER JOIN HumanResources.Employee m ON
e.ManagerID = m.EmployeeID

-----使用衍生表------

SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
INNER JOIN (SELECT SalesOrderID
          FROM Sales.SalesOrderDetail
          WHERE UnitPrice BETWEEN 1000 AND 2000) d ON
     s.SalesOrderID=d.SalesOrderID
     
     
     
-----使用 UNION 组合结果集-----

SELECT SalesPersonID,GETDATE() QuotaDate,SalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota>0
UNION
SELECT SalesPersonID,QuotaDate,SalesQuota
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota>0
ORDER BY SalesPersonID DESC,QuotaDate DESC