Self Join

/*
自我TABLE JOIN 可用於:
1.Parent-Child Relationship
2.自我比對
*/

---使用北風資料庫
use Northwind

---1.Parent-Child Relationship
------列出全部員工ID及其主管ID
Select EmployeeID,ReportsTo
From dbo.Employees
------列出全部員工姓名及其主管姓名
Select A.LastName as '員工' ,B.LastName as '主管'
From dbo.Employees A left join dbo.Employees B on A.ReportsTo=B.EmployeeID
------沒有主管的就是'BOSS'
Select A.LastName as '員工' ,IsNull(B.LastName,'BOSS') as '主管'
From dbo.Employees A left join dbo.Employees B on A.ReportsTo=B.EmployeeID
------使用Case When 表示'BOSS'
Select A.LastName as '員工' ,
case when B.LastName is null then 'Boss'
else B.LastName
end as '主管'
From dbo.Employees A left join dbo.Employees B ---假設A是員工,B是主管
on A.ReportsTo=B.EmployeeID ---假設條件成立(員工的主管編號=主管的員工編號)

---2.自我比對
------相同年資的員工年齡比對(年紀大在前,年紀小在後)
Select A.LastName,A.BirthDate,Datediff(month,A.HireDate,getdate())/12,B.LastName,B.BirthDate
from dbo.Employees A join dbo.Employees B ---假設A是年紀大,B是年紀小
on A.BirthDate＜B.BirthDate and Datediff(month,A.HireDate,getdate())/12=Datediff(month,B.HireDate,getdate())/12
------訂單中買相同產品,不同售價比對(大在前)
Select A.OrderID,A.ProductID,A.UnitPrice,B.OrderID,B.UnitPrice
from dbo.[Order Details] A join dbo.[Order Details] B
on A.UnitPrice>B.UnitPrice and A.ProductID=B.ProductID
-----訂單中同一客戶買相同產品,不同售價比對(大在前)
With Allorders ---CTE
as( Select O.OrderID,CustomerID,OrderDate,ProductID,UnitPrice,Quantity
From dbo.Orders O join dbo.[Order Details] D on O.OrderID=D.OrderID
)
Select A.OrderID,A.OrderDate,A.CustomerID,A.ProductID,A.UnitPrice,B.OrderID,B.OrderDate,B.UnitPrice
from Allorders A join Allorders B
on A.UnitPrice>B.UnitPrice and A.CustomerID=B.CustomerID and A.ProductID=B.ProductID