多种联结语句

 

-----内联结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