-- SQL create view with CTE (Common Table Expression) USE AdventureWorks GO CREATE VIEW vSalesStaffQuickStats AS WITH SalesBySalesPerson (SalesPersonID, NumberOfOrders, MostRecentOrderDate) AS ( SELECT SalesPersonID, COUNT(*), MAX(OrderDate) FROM Sales.SalesOrderHeader GROUP BY SalesPersonID ) SELECT E.EmployeeID, EmployeeOrders = OS.NumberOfOrders, EmployeeLastOrderDate = OS.MostRecentOrderDate, E.ManagerID, ManagerOrders = OM.NumberOfOrders, ManagerLastOrderDate = OM.MostRecentOrderDate FROM HumanResources.Employee AS E INNER JOIN SalesBySalesPerson AS OS ON E.EmployeeID = OS.SalesPersonID LEFT OUTER JOIN SalesBySalesPerson AS OM ON E.ManagerID = OM.SalesPersonID GO -- T-SQL test view SELECT * FROM vSalesStaffQuickStats ORDER BY EmployeeID GO
转载于:https://www.cnblogs.com/Dannier/archive/2012/08/09/2629901.html