CTE: common table expressions
可以用来生成临时数据,也可以用来表达递归
两个例子
With Topsales (SalesPersonID,NumSales) as
(select SalesPersonID, count(*)
from Sales.SalesOrderHeader group by salespersonID
)
select * from Topsales
With managers (managerID,employeeID) as
(
select managerID , employeeID from humanresources.employee
where managerID is null
union all
select e.managerID, e.employeeID
from humanresources.employee e
inner join Managers mgr
on e.managerID=mgr.employeeID
)
Select * from Managers